Home

Pivot Table: Two-way Show Values As:

%3CLINGO-SUB%20id%3D%22lingo-sub-522607%22%20slang%3D%22en-US%22%3EPivot%20Table%3A%20Two-way%20Show%20Values%20As%3A%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-522607%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20PivotTable%20shown%20below%20is%20based%20on%20installed%20pipe%20length%20source%20data%20in%20a%20separate%20tab.%3C%2FP%3E%3CP%3EThe%20PivotTable%20sums%20those%20lengths%20and%20shows%20them%20as%20running%20totals%20by%20year%20(row)%20for%20each%20material%20(column).%20This%20shows%20the%20cumulative%20amount%20of%20any%20material%20installed%20up%20to%20the%20year%20on%20that%20row.%3C%2FP%3E%3CP%3E%3CSPAN%3EFor%20example%3A%20Up%20to%201943%2C%20there%20had%20accumulated%20117%20ft%20of%20pipe%20material%20%22CAS%22%20installed.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E30%20ft%20in%201922%2C%200%20ft%20in%201942%2C%2087%20ft%20in%201943%20(30%2B0%2B87%3D117)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%3A%20I%20want%20to%20compute%20the%20percentage%20each%20cumulative%20total%20is%20of%20the%20grand%20total.%3C%2FP%3E%3CP%3ESo%2C%20the%20117%20ft%20then%20formed%2027%25%20of%20the%20cumulative%20system%20total%20(430%20ft)%20installed%20up%20through%201943.%3C%2FP%3E%3CP%3EImplemented%20on%20the%20whole%20PivotTable%20this%20would%20show%20how%20the%20composition%20of%20the%20system%20changed%20over%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20see%20a%20way%20to%20automate%20that%20second%20computation%20as%20a%20PivotTable%20feature.%3C%2FP%3E%3CP%3EI%20have%20fallen%20back%20on%20a%20manual%20%22table%22%20to%20the%20right%20of%20the%20PivotTable%20divides%20each%20cumulative%20value%20by%20the%20value%20in%20the%20Grand%20Total%20column%20on%20that%20row.%3C%2FP%3E%3CP%3EIs%20there%20an%20automated%20way%20to%20do%20that%20instead%3F%20Or%20perhaps%20a%20better%20work%20around%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111890iAFA631E088E166EC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Slide1.PNG%22%20title%3D%22Slide1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-522607%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPivotTable%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-524823%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%3A%20Two-way%20Show%20Values%20As%3A%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-524823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F332537%22%20target%3D%22_blank%22%3E%40pwilson4500%3C%2FA%3E%26nbsp%3B%2C%20did%20you%20try%20this%20option%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20383px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111919iEFEDC44C4909BA9A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-524897%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%3A%20Two-way%20Show%20Values%20As%3A%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-524897%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EI%20have%20tried%20that%20option.%20It%20shows%20every%20source%20data%20value%20in%20the%20table%20as%20a%20%25%20of%20the%20single%20Grand%20Total%20at%20the%20bottom%20right%20of%20the%20PivotTable.%20(The%20source%20data%20is%20not%20shown%20in%20my%20example.)%3C%2FP%3E%3CP%3EThe%20values%20in%20the%20example%20are%20already%20%22Show%20Values%20As%3A%20Running%20Total%20in%20Years%22.%3C%2FP%3E%3CP%3EI%20need%20to%20take%20that%20computed%20result%20and%20compute%20its%20percentage%20of%20the%20Grand%20Total%20for%20its%20same%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-525006%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%3A%20Two-way%20Show%20Values%20As%3A%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-525006%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F332537%22%20target%3D%22_blank%22%3E%40pwilson4500%3C%2FA%3E%26nbsp%3B%2C%20if%20creating%20the%20PivotTable%20you%20added%20your%20data%20to%20data%20model%2C%20you%20may%20create%20the%20measure%20to%20show%20running%20total%20in%20%25.%20For%20maintenability%20better%20to%20split%20it%20on%20two%2C%20running%20total%20itself%2C%20like%3C%2FP%3E%0A%3CPRE%3E%3DCALCULATE(SUM(%5BValue)%2CFILTER(%20ALL(Range%5BYear%5D)%2C%5BYear%5D%26lt%3B%3DMAX(%5BYear%5D)%20))%3C%2FPRE%3E%0A%3CP%3Elet%20name%20it%20RT%20(it%20shall%20show%20exactly%20the%20same%20what%20you%20have%20now)%2C%20and%20RT%25%20as%3C%2FP%3E%0A%3CPRE%3E%3D%5BRT%5D%2FCALCULATE(SUM(%5BValue%5D)%2CALL(Range%5BYear%5D))%3C%2FPRE%3E%0A%3CP%3EHere%20Range%20is%20the%20source%20table%2Frange.%3C%2FP%3E%0A%3CP%3ESimplest%20way%20to%20add%20measure%20is%20right%20click%20on%20your%20table%20name%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20277px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F111926i5BA8700ED6DEB11C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EQuite%20simple%20sample%20is%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E
pwilson4500
New Contributor

The PivotTable shown below is based on installed pipe length source data in a separate tab.

The PivotTable sums those lengths and shows them as running totals by year (row) for each material (column). This shows the cumulative amount of any material installed up to the year on that row.

For example: Up to 1943, there had accumulated 117 ft of pipe material "CAS" installed.

30 ft in 1922, 0 ft in 1942, 87 ft in 1943 (30+0+87=117)

 

Now: I want to compute the percentage each cumulative total is of the grand total.

So, the 117 ft then formed 27% of the cumulative system total (430 ft) installed up through 1943.

Implemented on the whole PivotTable this would show how the composition of the system changed over time.

 

I don't see a way to automate that second computation as a PivotTable feature.

I have fallen back on a manual "table" to the right of the PivotTable divides each cumulative value by the value in the Grand Total column on that row.

Is there an automated way to do that instead? Or perhaps a better work around?

Slide1.PNG

3 Replies

@pwilson4500 , did you try this option?

image.png

@Sergei BaklanI have tried that option. It shows every source data value in the table as a % of the single Grand Total at the bottom right of the PivotTable. (The source data is not shown in my example.)

The values in the example are already "Show Values As: Running Total in Years".

I need to take that computed result and compute its percentage of the Grand Total for its same row.

 

@pwilson4500 , if creating the PivotTable you added your data to data model, you may create the measure to show running total in %. For maintenability better to split it on two, running total itself, like

=CALCULATE(SUM([Value),FILTER( ALL(Range[Year]),[Year]<=MAX([Year]) ))

let name it RT (it shall show exactly the same what you have now), and RT% as

=[RT]/CALCULATE(SUM([Value]),ALL(Range[Year]))

Here Range is the source table/range.

Simplest way to add measure is right click on your table name here

image.png

Quite simple sample is attached

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies