Help Needed

Not applicable
Hi all! I’m not sure if this possible, but scenario is: I have a pivot table where each field value is summarised by SUM, and as a result, the Grand Total row is also showing the sum of the values. What I want is, instead of the Grand Total showing the SUM, I want it to show the result of a formula. An example formula would be =sum(nameofthefield)/2/8. I want this formula on the grand total only, the rest of the field can be summarised by SUM. Hope this makes sense!
1 Reply
best response


In general yes, but that depends on how your data is structured. For such sample


creating PivotTable add data in data model. Create DAX measure like

VAR sumV=SUM(Table1[V])
RETURN IF( COUNTROWS(VALUES(Table1[A]))=1, sumV, sumV/2/8)

and use it instead of SUM aggregation. The logic of the measure - if you have only one A selected, you are on row and use SUM(), if more than one your are on Total and we use modified SUM().