cancel
Showing results for
Did you mean:
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

## 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
3 REPLIES 3
Highlighted 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
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

## Re: Three variable Ratio in DAX

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

Announcements #### Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers! #### June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news. #### 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

Features releasing from October 2020 through March 2021 Top Solution Authors
Top Kudoed Authors
Users online (1,845)