Pivot Table output help  [Solved]

Anything related to PowerPivot and DAX Formuale
sash13
Posts: 2
Joined: Wed May 16, 2018 1:47 am

Pivot Table output help

Postby sash13 » Wed May 16, 2018 1:58 am

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.
Attachments
Pic1.JPG
Pic1.JPG (80.48 KiB) Viewed 594 times

RamanaV
Posts: 12
Joined: Thu Oct 19, 2017 12:57 pm

Re: Pivot Table output help  [Solved]

Postby RamanaV » Wed May 16, 2018 9:11 am

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%

sash13
Posts: 2
Joined: Wed May 16, 2018 1:47 am

Re: Pivot Table output help

Postby sash13 » Thu May 17, 2018 12:12 am

Now I get it, very simple.

Thank you so much for your reply. :)


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: No registered users and 5 guests