Forum Discussion

rishtin's avatar
rishtin
Frequent Visitor
6 years ago
Solved

Three variable Ratio in DAX

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 an...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
rishtin
Frequent Visitor
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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