Forum Discussion

Johel_D's avatar
Johel_D
Frequent Visitor
2 years ago
Solved

Compare table results

Hello All, I need some help with some results that I am getting on one of my dashboards.

 

What I need is to get the ratio between the two numbers, however, when I do the calculations the results are not correct, for instance:

Earn hours:

 

Paid hours: 

 

Ratio calculated by Power BI using DIVIDE formula:

Calculate NLE = DIVIDE(SUM('Earn hours'[ Earn Hours]), SUM('Paid hours'[Hours]), BLANK())
 

Nov 15: 13.15 / 10.28 = 8.16%

Nov 16: 10.68 / 10.57 = 6.63%

Nov 17: 3.32 / 8.55 = 2.06%

Nov21: 2.95 / 10.25 = 1.83%

 

 

Ratio calculated by Power BI using that I currently have:

WCG NLE =DIVIDE(SUM('Earn hours'[ Earn Hours]), AVERAGE('Paid hours'[Hours]))
 

Nov 15: 13.15 / 10.28 = 179.47%

Nov 16: 10.68 / 10.57 = 145.82%

Nov 17: 3.32 / 8.55 = 45.32%

Nov21: 2.95 / 10.25 = 40.29%

 

 

The result of this formula is a little bit closer but it is still off.

 

The results that I am expecting are: 

Nov 15: 13.15 / 10.28 = 127.9%

Nov 16: 10.68 / 10.57 = 101%

Nov 17: 3.32 / 8.55 = 38.8%

Nov21: 2.95 / 10.25 = 28.78%

 

The summaries in the tables "Earn hours" and "Paid hours" are correct, however, as you can see the calculations are incorrect.

Please let me know if this information is enough, the model comes from a huge database so I complicated to share a usefull model.

 

Thanks in advance for your help.

 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

     Hi @ Johel_D

     

    Based on your question, I have created the following two tables

     

     

    Then you need to create a relationship for the two tables in the Model View

     

     

    Then you can use your formula to get the results you want

     

    Measure =

DIVIDE(

    SUM('Earn hours'[Earn Hours]),

    SUM('Paid hours'[Hours])

)

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

     Hi @ Johel_D

     

    Based on your question, I have created the following two tables

     

     

    Then you need to create a relationship for the two tables in the Model View

     

     

    Then you can use your formula to get the results you want

     

    Measure =

DIVIDE(

    SUM('Earn hours'[Earn Hours]),

    SUM('Paid hours'[Hours])

)

     

     

     

     

     

     

    Best Regards,

    Jayleny

     

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