SOLVED

Help Needed

%3CLINGO-SUB%20id%3D%22lingo-sub-1985228%22%20slang%3D%22en-US%22%3EHelp%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1985228%22%20slang%3D%22en-US%22%3EHi%20all!%20I%E2%80%99m%20not%20sure%20if%20this%20possible%2C%20but%20scenario%20is%3A%20I%20have%20a%20pivot%20table%20where%20each%20field%20value%20is%20summarised%20by%20SUM%2C%20and%20as%20a%20result%2C%20the%20Grand%20Total%20row%20is%20also%20showing%20the%20sum%20of%20the%20values.%20What%20I%20want%20is%2C%20instead%20of%20the%20Grand%20Total%20showing%20the%20SUM%2C%20I%20want%20it%20to%20show%20the%20result%20of%20a%20formula.%20An%20example%20formula%20would%20be%20%3Dsum(nameofthefield)%2F2%2F8.%20I%20want%20this%20formula%20on%20the%20grand%20total%20only%2C%20the%20rest%20of%20the%20field%20can%20be%20summarised%20by%20SUM.%20Hope%20this%20makes%20sense!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1985228%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1985315%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20Needed%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1985315%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F901137%22%20target%3D%22_blank%22%3E%40Maryse1227%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20yes%2C%20but%20that%20depends%20on%20how%20your%20data%20is%20structured.%20For%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20386px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F240161iDFBC35AB58EA85E1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ecreating%20PivotTable%20add%20data%20in%20data%20model.%20Create%20DAX%20measure%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EVGT%3A%3D%0AVAR%20sumV%3DSUM(Table1%5BV%5D)%0ARETURN%20IF(%20COUNTROWS(VALUES(Table1%5BA%5D))%3D1%2C%20sumV%2C%20sumV%2F2%2F8)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20use%20it%20instead%20of%20SUM%20aggregation.%20The%20logic%20of%20the%20measure%20-%20if%20you%20have%20only%20one%20A%20selected%2C%20you%20are%20on%20row%20and%20use%20SUM()%2C%20if%20more%20than%20one%20your%20are%20on%20Total%20and%20we%20use%20modified%20SUM().%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
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 confirmed by Maryse1227 (New Contributor)
Solution

@Maryse1227 

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

image.png

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().