SAC - Subtotal is 100 %

[jeroenpar]

Dear all,

For a customer I am building a report in SAC which presents the production per employee. The customer wants to see the percentage of production per product per employee, where the total production of the employee is 100%.

The problem is that if I calculate the percentage of production the percentage is calculated over the grand total and not the subtotal per employee (which makes sense though).

Is there a possiblity to calculate the percentage over the subtotal per employee as 100%? Please see below an exemple.

There are about 200 employees so building a table per employee is not a desired solution...

Thank you so much for your help!

KR,

Jeroen

[jeroenpar]

Here below the example...

[Michael_Fox]

Hi Jeroen - yes, the trick is to calculate the Subtotal first as a Restricted Measure with Constant Dimension Selection:

In your case, instead of RowId as used in my example, you would use Product as your Constant Dimension.

Once you have this subtotal, then it is a simple Calculated Measure to get the percentage dividing the primary measure (production in your case) by the Total per Employee that you just created. Sample table below:

A few more details about creating such Restricted Measures can be found here.

Hope this helps.

Mike

[Susanne_Helbig]

Dear michael.fox3,

thanks for this detailed explanation, I really like your solution to show the % of Subtotal.

I've the requirement to also show the Total (means 100%) per constant dimension.
I tried to activate 'Show Totals' for 'Cost Center Activity Type' column (which is my constant dimension), but this is throwing the below error:

Do you know a trick how to overcome this issue?

Best regards
Susanne

[Michael_Fox]

Hi susannehelbig - I have distant memory of seeing exception 1000013 in different scenario, not this one. From what I recall, that had something to do with number formatting (periods vs. commas). I'm not sure of the specifics in your example shown, but maybe this gives you some clues to investigate???

[Susanne_Helbig]

Hi michael.fox3, thanks for your reply.

Actually the issue was not reproducible after some time, so no need to investigate further.

Nevertheless, while working with these % Sutotals, we realized that it could make sense to create them in the model, which allows us to add an INVERSE formula.
With this option, we enable users to also enter percentage values and not only absolute values, this offers more flexibility.

Thanks and best regards
Susanne

[LucasManechine]

Many thanks, great and direct post. Works perfectly here!!

[lcastelang]

Michael,

Your suggestion helped me...thank you very much.

Leopoldo

[benhaddou]

Hi Leopoldo,

Could you please tell me how I can obtain the total and total per employee?

Br.

Thami