Forum Discussion

noble10's avatar
noble10
Frequent Visitor
4 years ago
Solved

Ratio calculation

Hello 

 

Can someone please show me a measure of how to calculate the ratio of two numbers. The screenshot below shows how I completed the ratio in excel but how do I achieve this through power bi dax measure?  Thanks!

 

 

  • v-robertq-msft's avatar
    v-robertq-msft
    4 years ago

    Hi, noble10 

    According to your description, I can clearly understand your requirement, you can try to create a calculated column like this:

    Ratio =

var _GCD=GCD([item],[product])

return

DIVIDE([product],_GCD)&":"&DIVIDE([item],_GCD)

     

    And you can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • VahidDM's avatar
    VahidDM
    Super User
    4 years ago

    Hi noble10 

    Try this:

     

    Measure = 
    VAR _A =
        MAX ( [Production] ) // X in the excel formula
    VAR _B =
        MAX ( [Item] ) // Y in the excel formula
    VAR _GCD =
        GCD ( _A, _B )
    RETURN
        _A / _GCD & ":" & _B / _GCD

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!

     

    • noble10's avatar
      noble10
      Frequent Visitor
      4 years ago

      VahidDM  

       

      Thank you for your help. Unfortunately that formula above did not work. Production and Items are measures that I have created. I got the error message below. 

       

       

       

       

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    4 years ago

    Hi, noble10 

    According to your description, I can clearly understand your requirement, you can try to create a calculated column like this:

    Ratio =

var _GCD=GCD([item],[product])

return

DIVIDE([product],_GCD)&":"&DIVIDE([item],_GCD)

     

    And you can get what you want, like this:

     

    You can download my test pbix file below

    Thank you very much!

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.