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

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    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