Discussion Help Needed in Excel
https://techcommunity.microsoft.com/t5/excel/help-needed/m-p/1985228#M83668
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!Mon, 14 Dec 2020 13:13:23 GMTDeleted2020-12-14T13:13:23ZHelp Needed
https://techcommunity.microsoft.com/t5/excel/help-needed/m-p/1985228#M83668
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!Mon, 14 Dec 2020 13:13:23 GMThttps://techcommunity.microsoft.com/t5/excel/help-needed/m-p/1985228#M83668Deleted2020-12-14T13:13:23ZRe: Help Needed
https://techcommunity.microsoft.com/t5/excel/help-needed/m-p/1985315#M83671
<P><LI-USER uid="901137"></LI-USER> </P>
<P>In general yes, but that depends on how your data is structured. For such sample</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 386px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/240161iDFBC35AB58EA85E1/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>creating PivotTable add data in data model. Create DAX measure like</P>
<LI-CODE lang="excel">VGT:=
VAR sumV=SUM(Table1[V])
RETURN IF( COUNTROWS(VALUES(Table1[A]))=1, sumV, sumV/2/8)</LI-CODE>
<P>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().</P>Mon, 14 Dec 2020 13:46:11 GMThttps://techcommunity.microsoft.com/t5/excel/help-needed/m-p/1985315#M83671Sergei Baklan2020-12-14T13:46:11Z