Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Add Calculation: Percentage in a Pivot table

Received Response
131
Views
2
Comments
Jose Casquero
Jose Casquero Rank 4 - Community Specialist
in Oracle Analytics Forum

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 LabelsDeliveredShippedWIPGrand Total
2017Q3-071515
2017Q3-082020
2017Q3-091515
2017Q3-103535
2017Q3-11437151
2017Q3-121191131
2017Q3-1353035

   

but i need to do this:

    

Row LabelsDeliveredShippedWIPGrand Total
2017Q3-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,

Answers

  • Satya Surisetti
    Satya Surisetti Rank 2 - Community Beginner

    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
    Jose Casquero Rank 4 - Community Specialist

    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.