topic Using TOTAL in pivot table in App Development

Using TOTAL in pivot table

Giel0 — Fri, 30 Jun 2023 13:49:29 GMT

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!

Re: Using TOTAL in pivot table

Zapparoli — Fri, 30 Jun 2023 14:30:15 GMT

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

Re: Using TOTAL in pivot table

Giel0 — Mon, 03 Jul 2023 07:22:16 GMT

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?

Re: Using TOTAL in pivot table

marcus_sommer — Mon, 03 Jul 2023 12:01:35 GMT

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)

Re: Using TOTAL in pivot table

Giel0 — Tue, 04 Jul 2023 13:24:21 GMT

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