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
