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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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')) 
    


  • AJ2020
    AJ2020 ⚪️

    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

  • AJ2020
    AJ2020 ⚪️

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

Sign In or Register to comment.

Hey, Stranger!

It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!