05-02-2019 05:39 PM
05-02-2019 05:39 PM
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?
05-03-2019 04:31 AM - edited 05-03-2019 04:35 AM
@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.
05-03-2019 04:54 AM
@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
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