Page 1 of 1

Pivot Table output help

Posted: Wed May 16, 2018 1:58 am
by sash13
Hello, I am fairly new in using Power Pivot and Dax so I will try to be very detailed

Please see attached photo for better explanation

I have Data Model with next columns:

Unit Name Premium Losses Ratio (Losses/Premiums)
A $100 $50 50%
A $100 $50 50%
B $100 $25 25%
B $100 $25 25%

and when I create a Pivot table using this data model I get next:

Unit Name SumOfPremium SumOfLosses SumOfRatio
A $200 $100 100%
B $200 $50 50%

The problem is the last column in my pivot table, where I do not need it to return sum of ratios but the ratio (SumOfLosses/SumOfPremiums).
that would look like this:
Unit Name SumOfPremium SumOfLosses SumOfRatio
A $200 $100 50%
B $200 $50 25%


Thanks in advance for your help.

Re: Pivot Table output help  [Solved]

Posted: Wed May 16, 2018 9:11 am
by RamanaV
You need to first load your table into Data Model.
- Click on Power Pivot tab, and then on Add to Data Model.

Next, write 3 measures.
To write a measure, click on Power Pivot tab, and then on Measures, and then on New Measure.

Total Premium = SUM(DataTable[Premium])
Total Losses = SUM(DataTable[Losses])
Total Ratio % = DIVIDE([Total Losses], [Total Premium])

Now, you will get the correct result.

Row Labels Total Premium Total Losses Total Ratio %
A $200 $100 50%
B $200 $50 25%

Re: Pivot Table output help

Posted: Thu May 17, 2018 12:12 am
by sash13
Now I get it, very simple.

Thank you so much for your reply. :)