cancel
Showing results for
Did you mean:
AbhishekH 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 Company Category Fields Year Value A Total Business Net Claims Incurred 2015 70 A Total Business Net Premium Earned 2015 120 A Health Net Claims Incurred 2015 30 A Health Net Premium Earned 2015 60 A Motor Net Claims Incurred 2015 40 A Motor Net Premium Earned 2015 60 B Total Business Net Claims Incurred 2015 70 B Total Business Net Premium Earned 2015 120 B Health Net Claims Incurred 2015 30 B Health Net Premium Earned 2015 60 B Motor Net Claims Incurred 2015 40 B Motor Net Premium Earned 2015 60

Rows I would like to add to the data

 Company Category Fields Year Value A Total Business Net Loss Ratio 2015 Calculation A Health Net Loss Ratio 2015 Calculation A Motor Net Loss Ratio 2015 Calculation B Total Business Net Loss Ratio 2015 Calculation B Health Net Loss Ratio 2015 Calculation B Motor Net Loss Ratio 2015 Calculation

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

Kind regards,

Abhishek

1 ACCEPTED SOLUTION

Accepted Solutions 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. 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). 3. Click Raw data table(Table1)->Edit Query, click the Append Queries. Select the Table3, you will get the expected result. The desired result. If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia

3 REPLIES 3 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. 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). 3. Click Raw data table(Table1)->Edit Query, click the Append Queries. Select the Table3, you will get the expected result. The desired result. If you have other issues, don't hesitate to let me know.

Best Regards,
Angelia

AbhishekH 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

## 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.

Regards

Benjamin

Announcements #### 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

Make sure you didn't miss any of the things that happened in the community in January! Top Solution Authors
Top Kudoed Authors
Users online (5,331)