Calculating ratios in dax

[Anonymous]

Hi all,

How do you calculate a ratio in DAX.

I am formulating a monthly report and want to include a ratio for CV Sent:Interviews in the past month.

CV sent would be a sum and Interviews is a count.

Both of these values have individual consultants attached to them. Ideally I'd like to be able to show a ratio for each consultant.

all help is appreciated, thanks

[harshnathani · Accepted solution]

Hi @Anonymous ,

Incase you want to get the ratio as 1:x .

Create a measure

Ratio 1 = DIVIDE( [Team CV Sent] , [Team Interviews])

Then concatenate 

Ratio_Calc = CONCATENATE("1", CONCATENATE(" : ",  [Ratio 1]))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[Jessica_Ballard]

I had the same issue and this formula worked for me, however, the ratio is not appearing in whole numbers. My result is 1:5.4637..... How can I get the result to show as just 1:5?

[Anonymous]

Use ROUNDUP with a 0 for the second argument when you're concatenating.

examples:

RATIO = CONCATENATE(ROUNDUP(Table[Measure],0), ":1") -- will display as '#:1'

RATIO = CONCATENATE("1:", ROUNDUP(Table[Measure],0)) --will display as '1:#'

[Pragati11]

Hi @Anonymous ,

You can achieve this by doing 2 calculations:

1. CV Sent
2. Interviews

Then create a 3rd calculation by concatenating these 2 calculations with a ":" sign.

Share some sample data so that a proper DAX can be suggested.

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 , 

Thank you for your help

I have written the two measures to put against each other.

What formula do I use to make them a ratio.

Measures written are attached.

[Pragati11]

Hi @Anonymous ,

You can create another dax as follows:

Ratio_Calc = CONCATENATE([Team Interviews], CONCATENATE(" : ", [Team CV Sent]))

Just see of this works.

Thanks,

Pragati

[Anonymous]

HI @Pragati11 

Thanks for your help again.

The ratio came out as below.

Is there any way to make this ratio a 1:(Number) ratio, as this would be far more useful

[Pragati11]

Hi @Anonymous ,

Do you mean you end up with 2 ratios? 

• 1:99
• 1:546

Thanks,

Pragati

[Anonymous]

Hi @Pragati11 

I mean that I want to end up with a simplified ratio, 

e.g 99 : 546 would be 1 : 5.515

[Pragati11 · Accepted solution]

Hi @Anonymous ,

Create a measure as follows:

Ratio Value = [Team CV Sent] / [Team Interviews]

Ratio_Calc = CONCATENATE("1", CONCATENATE(" : ", [Ratio Value]))

Thanks,

Pragati

[harshnathani · Accepted solution]

Hi @Anonymous ,

Incase you want to get the ratio as 1:x .

Create a measure

Ratio 1 = DIVIDE( [Team CV Sent] , [Team Interviews])

Then concatenate 

Ratio_Calc = CONCATENATE("1", CONCATENATE(" : ",  [Ratio 1]))

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!