cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I
Helper I

Re: Three variable Ratio in DAX

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

View solution in original post

3 REPLIES 3
Highlighted
Helper I
Helper I

Re: Three variable Ratio in DAX

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

View solution in original post

Highlighted
Frequent Visitor

Re: Three variable Ratio in DAX

Hi @paulq 

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

Highlighted
Helper I
Helper I

Re: Three variable Ratio in DAX

@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

Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors