Mar 31 2021 12:33 PM
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 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.
Apr 01 2021 03:41 AM
That is if you are not on Mac.
Creating PivotTable add data to data model
and when create two measures (in Power Pivot or right click on table name in PivotTable pane)
MD Average:=AVERAGE(_C70_ALMM_Extended[MD])
and
Sum by day MD Average:=SUMX( GROUPBY(_C70_ALMM_Extended, [RF]), [MD Average])
and use second one in PivotTable
Average of MD here is built-in measure and here is only to compare, you don't need it.
Please check in attached file.
Apr 01 2021 04:32 AM
Good one @Sergei Baklan
Another way to write the same measure...
Sum by day MD Average2 :=
SUMX (
VALUES ( _C70_ALMM_Extended[RF] ),
[MD Average]
)
Apr 01 2021 05:07 AM
You are right, thank you.
Apr 01 2021 06:40 AM
Apr 07 2021 12:13 PM
Jun 25 2021 07:44 AM
Jun 25 2021 07:56 AM
@estoll2440 , you are welcome