Percentage Summary Number using Beast Mode

user060355

I have a graph that displays my company's net income vs. our budget.  In order to calculate the net income, I use the following beast mode:

 

sum(case 
	when `Group 2 Name` = 'Sales' or `Group 2 Name` = 'Sales Returns' then `Amount`
	when `Group 2 Name` = 'Direct Costs' or `Group 2 Name` = 'Protein Powder' then -1 * `Amount`
	when `Group 0 Name` = 'Other Income/Expense' then `Amount`
    else -1 * `Amount`
end)

 

My budget is stored in a field called: 

Net Income (b)

 

To generate the summary number, I did the following, but for some reason it returns 0%:

sum(case 
	when `Group 2 Name` = 'Sales' or `Group 2 Name` = 'Sales Returns' then `Amount`
	when `Group 2 Name` = 'Direct Costs' or `Group 2 Name` = 'Protein Powder' then -1 * `Amount`
	when `Group 0 Name` = 'Other Income/Expense' then `Amount`
    else -1 * `Amount`
end) / sum(`Net Income (b)`)

 

What am I missing?  It might be worth noting that multiplying by 100 did nothing, it still returns 0.

GrantSmith

Hi @user060355 

Without seeing your data - How is your budget structured? Is that a yearly, monthly or daily budget?

 

You're likely comparing apples to oranges  in terms of your unit of measurement. The numerator is likely on a daily basis but your budget is probably on a non-daily scale.

 

You'll want to either utilize a MIN function instead of a SUM to pull the budget amount (assuming the data you have is for the same date range (days) as it it in budget (You couldn't have two separate budget values if you're looking at two years worth of data as it'd use the same budget number for those two years)

 

The better option would be to utilize a windowing function (talk to your CSM if you don't have this enabled in your instance - it's a feature switch) to calculate the minimum budget over the timeframe your budget is defined as.

 

MIN(MIN(`Net Income (b)`)) OVER(PARTITION BY YEAR(`Date`))

 

You could use that as your denominator. Again, this is all assuming your `Amount` field is recorded on a daily basis but your budget is on a non-daily basis (like yearly in the example beast mode above).

 

 

A helpful tip: Whenever I'm running into issues with a percentage I'll create two separate beast modes, one for the numerator (top) and one for the denominator (bottom) and see what numbers are being returned to see if anything is looking odd. This helps diagnose issues I typically have.