Using TOTAL in pivot table

[Giel0]

Hi,

I would like to know a product family's share of sales of the total sales. I want to plot this over a number of years in a pivot table. The function I use is sum(sales)/sum(TOTAL sales). See attached a simplified example. 

However, this calculates the total of all years together and not the total of the year which is displayed in the pivot table column. How can I realise this? 

NOTE:  the datamodel contains 10 years of data. With use of a filter the user can choose whether to display 1,2,3 years etc. So in the example attached a filter is placed for the years 2023,2022, 2021. 

Many thanks!

[Zapparoli · Accepted solution]

Hi @Giel0 

Can you try using:

sum(sales)/sum(TOTAL <Year> sales)

This will make that your "Year" Field, will be considerated on the calculation.

-Zapparoli

[Giel0]

Hi @Zapparoli 

Thanks, that works! 

However, the calculation is correct when no filters are being placed on a product family. When I filter by a specific product family to do an analysis, the total count is also filtered as 100%. How can I extend the set analysis so that the filter on "Product family" is excluded and the 'total sales' stays the same? 

[marcus_sommer · Accepted solution]

You need to add a set analysis which ignored certain or maybe all selections, for example:

sum(sales)/sum({< Productfamily > } TOTAL <Year> sales)

sum(sales)/sum({1} TOTAL <Year> sales)

[Giel0]

@marcus_sommer thanks, that's exactly what I was looking for!