Summary number for non-distinct rows

Hi,

We have 4 sales regions & each has a budget amount. I need to do a summary number that uses the sum of those 4 budgets. 2 regions have the same budget amount so COUNT(DISTINCT) won't work. Each row of the dataset details an expense for a labeled region and that row also contains that region's budget amount (I joined the budget table with the sales table).

I have a card for each region that filters for just that region and I made a beast mode for the summary field to show the amount of budget they have used so far. This works fine for the individual region cards. But on a card I made (with no filter) to summarize all the regions together, the summary number goes to a crazy 674%. Is there a better way to get the usage % for all regions?

This is my current beast mode:

(CONCAT(ROUND(SUM(`ExpenseAmount`) /

  ((CASE WHEN `Region` = 'Northwest' THEN MIN(`Budget`) ELSE 0 END)

   +(CASE WHEN `Region` = 'Southwest' THEN MIN(`Budget`) ELSE 0 END)

   +(CASE WHEN `Region` = 'Northeast' THEN MIN(`Budget`) ELSE 0 END)

   +(CASE WHEN `Region` = 'Southeast' THEN MIN(`Budget`) ELSE 0 END))     

 *100,0),'% Usage')) 


Thanks, Angela

Best Answers

Answers

  • I may not be understanding your data correctly, but couldn't you just do SUM(`Budget`) for your denominator rather than doing a case statement for the 4 regions? Like this:

    (CONCAT(ROUND((SUM(`ExpenseAmount`) /  SUM(`Budget`)) *100,0),'% Usage')) 
    


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    Don't JOIN Budget to Actuals. APPEND Budget to Actuals.


    By Appending you'll avoid duplication and can avoid 'crazy math'.

    https://www.youtube.com/watch?v=PVbOeLSae9o&t=18s

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • AJ2020
    AJ2020 Member
    edited March 2021 Answer ✓


  • Thanks @jaeW_at_Onyx ! The Append did the trick. At first I couldn't see the budgets bu then realized I needed to add a new date field that would pull in both budget lines and expense lines. I finally just cleaned up the last card and they look great!

    Thanks again!

    Angela

  • Thanks @MarkSnodgrass. That is definitely a cleaner statement :) but my issue went deeper to the actual dataset setup.