Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

  • Anonymous's avatar
    Anonymous
    6 years ago

    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!

     

     

  • Pragati11's avatar
    Pragati11
    6 years ago

    Hi Anonymous ,

     

    Create a measure as follows:

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

     

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

     

    Thanks,

    Pragati

10 Replies

  • Pragati11's avatar
    Pragati11
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Pragati11
        Icon for Super User rankSuper User
        6 years ago

        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

  • Jessica_Ballard's avatar
    Jessica_Ballard
    Frequent Visitor
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

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