Ratio calculation at lowest account

[Anonymous]

Hi I am looking to calculate within a table the expense ratio for multiple accounts, so that I can rollup their percentages to get to the total expense ratio. i.e. Rent Expense/ Earned Premium. Below is how I would like my table to look column C would be a calculated column that just divides column B by Earned premium.

Account	Amount	Ratio
Rent	150	37.5%
Salary	200	50.0%
Commission	180	45.0%
Earned Premium	400	100%

Thank you!

[JustJan]

Hi @Anonymous 

Assuming the 100% is always from the Earned Premium line, you could create a measure like: 

M1 =

Var AmountEP = calculate(sum('Table'[Amount]),'Table'[Account] = "Earned Premium")

return divide(sum('Table'[Amount]), AmountEP)

Format it as a percentage to complete your table. 

[az38]

Hi @Anonymous 

try a calculated column

Column = 
DIVIDE([Amount],
LOOKUPVALUE('Table'[Amount],'Table'[Account],"Earned Premium")
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[JarroVGIT]

You can download my example here:

https://1drv.ms/u/s!Ancq8HFZYL_aiIlEB5yDWLV_oX_pJw?e=6WJbLd

The calculated column is the following:

Ratio = DIVIDE('Table'[Amount], LOOKUPVALUE('Table'[Amount], 'Table'[Account], "Earned Premium"))

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[Anonymous]

Hi @JarroVGIT and @az38 ,

Thank you for the quick response! Is it possible to do this if the accounts repeat for multiple regions? i.e the below:

Region	Account	Amount	Ratio
East	Rent	150	37.5%
East	Salary	200	50.0%
East	Commission	180	45.0%
East	Earned Premium	400	100.0%
West	Rent	300	42.9%
West	Salary	400	57.1%
West	Commission	100	14.3%
West	Earned Premium	700	100.0%

[JarroVGIT]

Hi @Anonymous ,

You can use this column:

Ratio = DIVIDE('Table'[Amount], LOOKUPVALUE('Table'[Amount], 'Table'[Account], "Earned Premium", Table'[Region], [Region]))

Let me know if this works 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[az38 · Accepted solution]

@Anonymous 

Column = 
DIVIDE([Amount],
LOOKUPVALUE('Table'[Amount],'Table'[Account],"Earned Premium",'Table'[Region],[Region])
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Is there a way to take this one step further, so that I can calculate East's Ratios, West's Ratios and a Total Ratio? Right now I am running into issues where I have lines of business that rollup in a mapping table, but when I pull a parent LOB it sums up the underlying percentages rather than recalculating them

[JarroVGIT]

Something like this:

Ratio = 
IF(HASONEVALUE('Table'[Account]) && SELECTEDVALUE('Table'[Account]="Earned Premium",
   DIVIDE('Table'[Amount], LOOKUPVALUE('Table'[Amount], 'Table'[Account], "Earned Premium", Table'[Region], [Region])),
DIVIDE(
     CALCULATE(SUM('Table'[Amount]), FILTER(Table, 'Table'[Account] <> "Earned Premium")),
      CALCULATE(SUM('Table'[Amount]), FILTER(Table, 'Table'[Account] = "Earned Premium"))
)
)

[JarroVGIT]

Hi @Anonymous ,

Did this solve your issue?

[Anonymous]

Hi @JarroVGIT ,

I am running into an error where is says that my column cannot be found or may not be used in this expression for the selectedvalue formula. Any idea why this might be?

Thanks,

PS1018

[JarroVGIT]

Just a tip for future reference: typing out DAX without any intellisense is very hard and error prone. If you don't provide screenshots, (complete) example data, pbix etc, it is very hard for me to pinpoint the problem. Can you share screenshots of the DAX and error perhaps? 

[Anonymous]

Hi @JarroVGIT ,

Below is a screenshot of my Dax. I was using example data before that did not match my actual data. That is why the naming is different. 

[JarroVGIT]

In the first line, you have SELECTEDVALUE('HFM Extract'[HFMAccount]="4100000") which should be SELECTEDVALUE('HFM Extract'[HFMAccount])="4100000".

Note the bracket place. 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂