cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AbhishekH Frequent Visitor
Frequent Visitor

Calculate rows for financial data / ratio calculation

Hello,

I have following finance data for last 3 years and I would like add some rows to the data based on calculation. Is it possible to do this using power BI?

Bellow is the sample data which has similar structure to original data.

Raw data    
CompanyCategoryFieldsYearValue
ATotal BusinessNet Claims Incurred201570
ATotal BusinessNet Premium Earned2015120
AHealthNet Claims Incurred201530
AHealthNet Premium Earned201560
AMotorNet Claims Incurred201540
AMotorNet Premium Earned201560
BTotal BusinessNet Claims Incurred201570
BTotal BusinessNet Premium Earned2015120
BHealthNet Claims Incurred201530
BHealthNet Premium Earned201560
BMotorNet Claims Incurred201540
BMotorNet Premium Earned201560

 

Rows I would like to add to the data

CompanyCategoryFieldsYearValue
ATotal BusinessNet Loss Ratio2015Calculation
AHealthNet Loss Ratio2015Calculation
AMotorNet Loss Ratio2015Calculation
BTotal BusinessNet Loss Ratio2015Calculation
BHealthNet Loss Ratio2015Calculation
BMotorNet Loss Ratio2015Calculation

Calculation (Net Loss Ratio) = Net Claims Incurred / Net Premium Earned

 

 Thank you for your help

Kind regards,

Abhishek

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-huizhn-msft
Microsoft

Re: Calculate rows for financial data / ratio calculation

Hi @AbhishekH,

I try to reproduce your scenario and get expected result as the following steps.

1. Create new table.

New Table1 = SUMMARIZE(Table1,Table1[Company],Table1[Category],Table1[Year],"1",CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Fields]="Net Claims Incurred")),"2",CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Fields]="Net Premium Earned")))


Create calculated column to get ratio and add a fields column.

Fields = "Net Loss Ratio"

Value = 'New Table1'[Net Claims Incurred]/'New Table1'[Net Premium Earned]

 You will get the following table.

1.PNG

2. Please right click the new table->Copy Tbale, then click Enter Data->Paste, delete the third and forth column, you will get the following table(Table3).

2.PNG

 

3. Click Raw data table(Table1)->Edit Query, click the Append Queries. Select the Table3, you will get the expected result.

3.png

The desired result.

 

5.PNG

 

 

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia

 

View solution in original post

3 REPLIES 3
Microsoft v-huizhn-msft
Microsoft

Re: Calculate rows for financial data / ratio calculation

Hi @AbhishekH,

I try to reproduce your scenario and get expected result as the following steps.

1. Create new table.

New Table1 = SUMMARIZE(Table1,Table1[Company],Table1[Category],Table1[Year],"1",CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Fields]="Net Claims Incurred")),"2",CALCULATE(SUM(Table1[Value]),FILTER(Table1,Table1[Fields]="Net Premium Earned")))


Create calculated column to get ratio and add a fields column.

Fields = "Net Loss Ratio"

Value = 'New Table1'[Net Claims Incurred]/'New Table1'[Net Premium Earned]

 You will get the following table.

1.PNG

2. Please right click the new table->Copy Tbale, then click Enter Data->Paste, delete the third and forth column, you will get the following table(Table3).

2.PNG

 

3. Click Raw data table(Table1)->Edit Query, click the Append Queries. Select the Table3, you will get the expected result.

3.png

The desired result.

 

5.PNG

 

 

If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia

 

View solution in original post

AbhishekH Frequent Visitor
Frequent Visitor

Re: Calculate rows for financial data / ratio calculation

Hello Angelia,

Thank you for looking into my query and providing a solution. It is very helpful and I think it will be useful for me to do some more sophisticated calculations using your method.


Many thanks,
Abhishek





Highlighted
Zocker Occasional Visitor
Occasional Visitor

Re: Calculate rows for financial data / ratio calculation

Hi Angelia,

we have more or less the same issue. The lowest level is the profit center level and above we have two/three higher levels, where profit centers are summarized. The highest level is in the end the whole company.

Means we would do the same steps like you have described above to calculate a new KPI (in this case Net Loss Ratio).

 

On the lowest level (profit center level) the results are fine (each profit center has the value which is shown in the table) but as soon as we choose the next level (for example sum of 10 profit centers) the calculation for this measure is not working. 

 

Can you help us here to fix this issue? I guess the issue is that in our hierarchy we have several levels.

 

Thanks for your help.

 

Regards

Benjamin

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors