Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculating ratios in dax

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

2 ACCEPTED SOLUTIONS

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!

 

 

View solution in original post

Hi @Anonymous ,

 

Create a measure as follows:

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

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

View solution in original post

10 REPLIES 10
Jessica_Ballard
Frequent Visitor

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

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

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

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.

jcoughlan_0-1594635799980.png

jcoughlan_1-1594635821787.png

 

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

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

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

jcoughlan_0-1594637082440.png

 

Hi @Anonymous ,

 

Do you mean you end up with 2 ratios? 

  • 1:99
  • 1:546

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

Hi @Pragati11 

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

e.g 99 : 546 would be 1 : 5.515

Hi @Anonymous ,

 

Create a measure as follows:

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

 

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

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

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!

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors