Pivot Table - Showing Percentage

%3CLINGO-SUB%20id%3D%22lingo-sub-2485640%22%20slang%3D%22en-US%22%3EPivot%20Table%20-%20Showing%20Percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2485640%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20I%20am%20looking%20to%20show%20as%20part%20of%20this%20very%20basic%20pivot%20table%20is%20the%20%25%20of%20Fuel%20Units%20against%20the%20total%20for%20each%20of%20the%20branches.%20Not%20sure%20why%20I%20am%20struggling%3F%20I%20thought%20pulling%20the%20values%20of%20the%20fuel%20recharge%20units%20down%20again%20and%20showing%20the%20value%20as%20%25%20of%20row%20total%20would%20work%20but%20it%20doesnt%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LesleyF_0-1624630644103.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291372iD99CC2BA2E49B9A6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22LesleyF_0-1624630644103.png%22%20alt%3D%22LesleyF_0-1624630644103.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22LesleyF_1-1624630817799.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F291381iED85882E83CB88C0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22LesleyF_1-1624630817799.png%22%20alt%3D%22LesleyF_1-1624630817799.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2485640%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2485972%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20-%20Showing%20Percentage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2485972%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1088131%22%20target%3D%22_blank%22%3E%40LesleyF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20you%20would%20like%20to%20show%20per%20cent%20of%20Fuel%20Units%20to%20Total%20Units%2C%20not%20per%20cent%20to%20total%20in%20row.%20Since%20you%20have%20no%20columns%20which%20separates%20data%20on%20something%20(divisions%2C%20countries%2C%20whatever)%2C%20total%20in%20row%20is%20equal%20to%20value%20in%20row%20(only%20one%20column%20for%20Fuel%20Unit).%20Thus%20always%20100%25.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20load%20data%20to%20data%20model%20you%20may%20add%20DAX%20measure%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DDIVIDE(SUM(Table%5BFuel%20Units%5D)%2C%20SUM(Table%5BTotal%20Units%5D)%2C%200)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eto%20show%20such%20per%20cent.%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%2C%20if%20without%20data%20model%2C%20calculated%20field%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3D%20'Total%20Units'%20%2F%20'Fuel%20Units'%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20sum%20it%20in%20aggregation.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Visitor

What I am looking to show as part of this very basic pivot table is the % of Fuel Units against the total for each of the branches. Not sure why I am struggling? I thought pulling the values of the fuel recharge units down again and showing the value as % of row total would work but it doesnt

 

LesleyF_0-1624630644103.png

LesleyF_1-1624630817799.png

 

 

1 Reply

@LesleyF 

I guess you would like to show per cent of Fuel Units to Total Units, not per cent to total in row. Since you have no columns which separates data on something (divisions, countries, whatever), total in row is equal to value in row (only one column for Fuel Unit). Thus always 100%.

 

If you load data to data model you may add DAX measure like

=DIVIDE(SUM(Table[Fuel Units]), SUM(Table[Total Units]), 0)

to show such per cent. 

Or, if without data model, calculated field like

= 'Total Units' / 'Fuel Units'

and sum it in aggregation.