Help Needed

[Maryse1227]

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!

[#2 · Accepted solution]

@Maryse1227 

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

VGT:=
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().