Forum

Notifications
Clear all

Need that in a pivot table the subtotal of column with averages is the sum of the items and not the average.

4 Posts
2 Users
0 Reactions
310 Views
(@marcberg)
Posts: 23
Trusted Member
Topic starter
 

Hello,

I need some help with totals and subtotals in a pivot table
I want that the subtotal row of a column with averages the sum is of the items and not the average of the items.
I attachment you can find in one tab the data and in the other tab the pivot table.
In the last three columns the value is the average. The rows are two lines days and  RF. 
For each day there is a subtotal of three RF lines. 
There are 9 columns. The first 6 rows are sums, the last three are averages.
For the subtotal of the day the sum of the three RF for each column has to be made.
For the first six columns there is no problem. For the last three (Average of MD, Average of RD and Average of ND) i get in the subtotal line the average of the 3 RF items and not the sum and i need the sum.

Can someone tell me how i can solve this ?

Thank You in advance.

Marc.

 
Posted : 01/04/2021 3:16 pm
(@mynda)
Posts: 4762
Member Admin
 

Hi Marc,

No file attached, however this behaviour is by design. You need to use Power Pivot and write a DAX measure to modify how averages normally calculate.

Mynda

 
Posted : 02/04/2021 5:32 am
(@marcberg)
Posts: 23
Trusted Member
Topic starter
 

i'm sorry, i attached the file but i didn't upload it.
Now it is hopefully attached.

 
Posted : 02/04/2021 12:58 pm
(@mynda)
Posts: 4762
Member Admin
 

Hi Marc,

Please see file attached. I added your data to Power Pivot and added 3 DAX measures to sum the averages. Hope that helps.

If you'd like to learn Power Pivot, please consider my Power Pivot course.

Mynda

 
Posted : 03/04/2021 2:50 am
Share: