Forum Discussion
Pivot table custom total formula
Hello, I have created a pivot table, however I am stuck on 3 calculations, 2 of which are show on the attached screenshot. I am trying to see how I can customize a specific cell calculation. ...
Adding data to data model you may add DAX measure like
PerCent:=IF(HASONEVALUE(Table[Date]),
AVERAGE(Table[Percent]),
SUM(Table[Daily GP])/SUM(Table[Daily sales])
)
I found where you were referencing but receive the following attached error once trying to "Add New Measure"?? Thanks.
Vin
It's bit hard to discuss that with screenshots only.
First, I assume you are on Excel for Windows desktop on version which works with data model (majority of them).
Creating PivotTable check the box as on screenshot to add data to data model
For this sample you may add measure as
Percent Measure:=IF(HASONEVALUE(Table1[Date]), AVERAGE(Table1[Percent]), SUM(Table1[Profit])/SUM(Table1[Sales]) )The idea is takes average of profit if only one day selected (e.g. for each row of the PivotTable) and if few days are selected (i.e. for Grand Total) it calculates profit for total numbers, not average of averages. Result is