Forum Discussion

pwilson4500's avatar
pwilson4500
Copper Contributor
May 03, 2019

Pivot Table: Two-way Show Values As:

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...
PivotTable
pwilson4500's avatar
pwilson4500
Copper Contributor
May 03, 2019

SergeiBaklanI 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.

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 03, 2019

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

Quite simple sample is attached

Resources