Total sales is 100% Calculation

Winnie_1234
Winnie_1234 Member
in Beast Mode

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.

image.png

Best Answer

  • ggenovese
    ggenovese Coach
    Answer ✓

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

Answers

  • ggenovese
    ggenovese Coach

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

    image.png
  • Winnie_1234
    Winnie_1234 Member

    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
    ggenovese Coach
    Answer ✓

    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
    Winnie_1234 Member

    Thank you very much! It works

Categories