Total sales is 100% Calculation

Winnie_1234

Hi, i have tried solving this calc for the whole day but to no avail.

Sincerely looking for help to calculate % of Sales in beast mode. I need my sales (of the selection whether by calendar type, location, custom period) to be always 100%, the sum of each category (numerator) will be divide by the total sales (14m in this case as denominator) to get the % of sales.

This is my current formula but it doesn't work as intended

SUM(Actual Custom) /
NULLIF(SUM(CASE
WHEN Header 1 = '01-Sales' THEN Actual Custom
ELSE 0
END) OVER(), 0)

Thanks.

ggenovese

I see, your data is structured with semi-additive measures, rather than additive measures. If the data was structured with additive measures then you could use the "% of Column" option, instead you have to create a beast mode because SUM(Actual Custom) is greater than 14,312,756, is that correct?

If so, I think a FIXED function will work for you…

SUM(Actual Custom) /
SUM(SUM(CASE WHEN Header 1 = '01-Sales' THEN Actual Custom END) FIXED ())

The above should work if there is only one '01-Sales', but if it's segmented by another dimension, like a Region for example, then you would need to indicate what it's fixed by:

SUM(Actual Custom) /
SUM(SUM(CASE WHEN Header 1 = '01-Sales' THEN Actual Custom END) FIXED (BY Region))

ggenovese

To get a % of Total you can select Show Value As, and then click % of Column Total

Winnie_1234

If i were to use % column total is not right for my scenario. as stated, my measures include sales, cost, income, for my calculation, i need to use 14,312,756 as my denominator, which later equivalent to 100%.

the suggestion will take sales + cost + income=100% .

ggenovese

I see, your data is structured with semi-additive measures, rather than additive measures. If the data was structured with additive measures then you could use the "% of Column" option, instead you have to create a beast mode because SUM(Actual Custom) is greater than 14,312,756, is that correct?

If so, I think a FIXED function will work for you…

SUM(Actual Custom) /
SUM(SUM(CASE WHEN Header 1 = '01-Sales' THEN Actual Custom END) FIXED ())

The above should work if there is only one '01-Sales', but if it's segmented by another dimension, like a Region for example, then you would need to indicate what it's fixed by:

SUM(Actual Custom) /
SUM(SUM(CASE WHEN Header 1 = '01-Sales' THEN Actual Custom END) FIXED (BY Region))

Winnie_1234

Thank you very much! It works