topic Re: Calculating ratios in dax in DAX Commands and Tips

Calculating ratios in dax

Anonymous — Tue, 07 Jul 2020 12:26:22 GMT

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

Re: Calculating ratios in dax

Pragati11 — Tue, 07 Jul 2020 13:26:15 GMT

Hi Anonymous ,

You can achieve this by doing 2 calculations:

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

Re: Calculating ratios in dax

Anonymous — Mon, 13 Jul 2020 10:25:10 GMT

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.

Re: Calculating ratios in dax

Pragati11 — Mon, 13 Jul 2020 10:34:01 GMT

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

Re: Calculating ratios in dax

Anonymous — Mon, 13 Jul 2020 10:45:35 GMT

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

Re: Calculating ratios in dax

Pragati11 — Mon, 13 Jul 2020 10:47:38 GMT

Hi Anonymous ,

Do you mean you end up with 2 ratios?

1:99
1:546

Thanks,

Pragati

Re: Calculating ratios in dax

Anonymous — Mon, 13 Jul 2020 10:57:47 GMT

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!

Re: Calculating ratios in dax

Anonymous — Mon, 13 Jul 2020 10:58:52 GMT

Hi Pragati11

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

e.g 99 : 546 would be 1 : 5.515

Re: Calculating ratios in dax

Pragati11 — Mon, 13 Jul 2020 11:01:29 GMT

Hi Anonymous ,

Create a measure as follows:

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

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

Thanks,

Pragati

Re: Calculating ratios in dax

Jessica_Ballard — Fri, 31 Jul 2020 21:29:38 GMT

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?

Re: Calculating ratios in dax

Anonymous — Wed, 21 Dec 2022 18:59:40 GMT

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:#'