Add Calculation: Percentage in a Pivot table

Jose Casquero

Summary

Add Calculation: Percentage in a Pivot table

Content

Hello,

i need to know how to add a calculation into my data analysis to able to create a pivot table:

it needs to calculate the right percentage for each column considering all the counts in the same row  as gran total.

I have this

    

Row Labels	Delivered	Shipped	WIP	Grand Total
2017Q3-07	15			15
2017Q3-08	20			20
2017Q3-09	15			15
2017Q3-10	35			35
2017Q3-11	43	7	1	51
2017Q3-12	11	9	11	31
2017Q3-13		5	30	35

   

but i need to do this:

    

Row Labels	Delivered	Shipped	WIP	Grand Total
2017Q3-07	100%	0%	0%	100%
2017Q3-08	100%	0%	0%	100%
2017Q3-09	100%	0%	0%	100%
2017Q3-10	100%	0%	0%	100%
2017Q3-11	84%	14%	2%	100%
2017Q3-12	35%	29%	35%	100%
2017Q3-13	0%	15%	85%	100%

Thanks guys,

Satya Surisetti

Hi,

Below are steps to prepare the output the way you want, there might be other alternative ways also.

Step 1 : Create 4 calculations, one for each ( Delivered, Shipped, WIP and Total).

1. Delivered %   -  Delivered /Sum(Delivered + Shipped +WIP)

2. Shipped % - Shipped / Sum(Delivered + Shipped +WIP)

3. WIP % - WIP / Sum(Delivered + Shipped +WIP)

4. Total % - Sum(Delivered + Shipped +WIP)/Sum(Delivered + Shipped +WIP)

Step 2: Add your Year Month Dimension and the above 4 calculated columns to a pivot visualization.

Step 3 : Go to properties of Pivot and select # and change the values number format to percent for all these four fact items.

Step 4 : check the refreshed pivot will show the values in desired format, change the dimension from month to another dimension to show if the numbers are reflecting as expected.

Below are screen shots of Pivot properties section and final pivot visualization as a sample. Let me know if you want the sample DVD project and i can share with you if you have any difficulties

Rgds

Satya Surisetti

Jose Casquero

Hi

Delivered, shipped and WIP are values in the same column , so i could not follow  your steps.

Finally, i found the solution by creating three different calculations using conditions and the attribute containing the values:

This way i created the following calculations:

- shipped - calculation

- delivered - calculation

- WIP- calculation

Afterwards, i created the percentages that i was looking for creating more calculations by using the previous calculations already done. I had to cast the value as doubles otherwise the system

will treat the values as integers, missing the information after coma.

Display:

I created a table by dragging CBD field/Attribute and the calculations created for the percentages into the rows feature of the graph.

Finally i went to the properties of the graph to show the values as percentages instead as numbers.