7 Replies Latest reply on Feb 27, 2017 6:03 AM by Tushar More

# Using Sub Totals in Calculated field

My requirement is to calculate percentage of sales by Store, Category and Product level. The percent should be counted using the subtotal value. Can anyone suggest how to achieve this?

• ###### 1. Re: Using Sub Totals in Calculated field

Hi Ayush,

See the attached screenshot.

Is this what you want?

Thanks,

Tushar

• ###### 2. Re: Using Sub Totals in Calculated field

Hi Ayush,

Not sure but find my approach as reference based on LOD-expression below and stored in attached workbook version 9.3 located in the original threat.

Step 1: Value per Store Category: { FIXED [Store],[Category]:sum([value])}

Step 2: Value per Store: { FIXED [Store]:sum([value])}

Step 3: % of Category: [value]/[Value per Category]

Step 4: % of Store: [value]/[Value per Store]

Step 5: Drag all defined calculated fields on the canvas

Regard,

Norbert

2 of 2 people found this helpful
• ###### 3. Re: Using Sub Totals in Calculated field

Hi Tushar,

Thanks for the help but my requirement is little different.

The percentage should be calculated using the subtotal instead of total for that column. Say for e.g., category 'Furniture' should have a total of 100% instead of 33.67%. That's my requirement.

• ###### 4. Re: Using Sub Totals in Calculated field

Hi Norbert,

Thanks for the the help.

I want toatl percentage and subtotal percentage base on category and store list. The trick part is that for store the total percentage should be exactly 100% and same goes with Category too. Also the grand total percentage should also be 100%.

• ###### 5. Re: Using Sub Totals in Calculated field

Hi Ayush,

Is this what you are looking for?

Thanks,

Tushar

• ###### 6. Re: Using Sub Totals in Calculated field

Hi Tushar,

This is what exactly I am looking for. Can you share how you have achieved it?

• ###### 7. Re: Using Sub Totals in Calculated field

Hi Ayush,

Here are steps:

1. Right click the measure in my case it is sum of sales.

2. Enable Subtotals,

3. Add Quick table calculation > percent to total.

4. Again R-click the same measure > compute using > sub-category.

Hope this helps you.

Thanks,

Tushar