Three variable Ratio in DAX

[rishtin]

Hello,

I have been using divide function in DAX to determine ratio between two numbers. So in this case the result is a single decimal number as an ouput. For Example, Ratio between 12, 6 is 2 and i get that by this formula divide(12,6). I can concatenate the result with ":1" to display it as 2:1.

Now I'm trying to determine ratio among three numbers e.g. Ratio of 12, 6, 18. What would be the best way to do this? Does anyone has experience with it?

thanks

[Anonymous · Accepted solution]

Hi @rishtin 

I would figure out which value is the smallest in your three columns, then divide it all by that value. I am assuming you're okay with decimal values and that the small value you want to see is 1. This will format it in the following format "2:1:3".

Ratio =

VAR SM = min(MIN('Table'[Column1],'Table'[Column2]),'Table'[Column3])

RETURN

DIVIDE('Table'[Column1],SM)&":"&DIVIDE('Table'[Column2],SM)&":"&DIVIDE('Table'[Column3],SM)

-Paul

[rishtin]

Hi @Anonymous 

Thanks for helping out. Yes, it makes sense to do it this way and yes I am ok with fractions.

Do you think it will make reading easier if we keep 1 at a fixed place. For example keeping 1 in the middle so the ratio would be read as Two to One to Three (2:1:3). I have yet to try it but maybe we can do that by dividing column1 and column3 by column2 always.

Also, when i concatenate text with the result, I lose the functionality of "Conditional Formatting". Is there any workaround to that?

Thanks

[Anonymous]

@rishtin 

I think keeping the 1 in the same place could help, but then it may be confusing if you're showing columns 1 through 3 but then the ratio as columns 1, 3, then 2.

Regarding formatting, I would format the number after you make the division, between the and signs. e.g. format(divide(x,y),*formattype*) & " : " etc