Beast Mode

Beast Mode

Total sales is 100% Calculation

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

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In

Best Answer

  • 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…

    1. SUM(Actual Custom) /
    2. 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:

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

Answers

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

    image.png
  • 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% .

  • 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…

    1. SUM(Actual Custom) /
    2. 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:

    1. SUM(Actual Custom) /
    2. SUM(SUM(CASE WHEN Header 1 = '01-Sales' THEN Actual Custom END) FIXED (BY Region))
  • Thank you very much! It works

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In