Add Calculation: Percentage in a Pivot table

Question

Summary

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 LabelsDeliveredShippedWIPGrand Total2017Q3-0715152017Q3-0820202017Q3-0915152017Q3-1035352017Q3-114371512017Q3-1211911312017Q3-1353035

but i need to do this:

Row LabelsDeliveredShippedWIPGrand Total2017Q3-07100%0%0%100%2017Q3-08100%0%0%100%2017Q3-09100%0%0%100%2017Q3-10100%0%0%100%2017Q3-1184%14%2%100%2017Q3-1235%29%35%100%2017Q3-130%15%85%100%

Thanks guys,

Jose Casquero · Answer

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.

Satya Surisetti · Answer

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