topic Re: Measure which divides two other measures giving incorrect results in DAX Commands and Tips

Measure which divides two other measures giving incorrect results

NMOORE — Thu, 15 Dec 2022 19:57:50 GMT

I've been stuck with an issue where I'm dividing two measures and getting an incorrect result in the table rows and totals - it is also incorrect if I just show the different calculations as cards. The measures reach out across multiple tables within the data model, I also have circa 100 other measures which work fine. I did have this all in one measure using variables but have broken it up to error check.

For context my measures are below and I've attached a picture. CALC_1 & CALC_2 work fine on their own, it's only when I go to simply divide them that the results are incorrect.

CALC_1 = SUM(vw_Monthly_Cost_Split[ACWP]) + SUM(vw_PAA_Cost[Current Control Budget]) + SUM(vw_PC_EV_Full[Cumultive])

CALC_2 = 
DIVIDE(
    SUM(vw_PC_EV_Full[Cumulative]),
    SUM(vw_PC_PV_Full[Cumulative])
)
*
DIVIDE(
    SUM(vw_PC_EV_Full[Cumulative]),
    SUM(vw_Monthly_Cost_Split[ACWP])
)

CALC_RESULT = DIVIDE([CALC_1],[CALC_2])

I've played around with SUMX, SUMMARIZE & RELATED functions with no luck, although I wasn't confident in my testing.

It's data I can't share but I'm hoping someone might know the issue straight away. I really appreciate any help.

Thanks

Re: Measure which divides two other measures giving incorrect results

Anonymous — Fri, 16 Dec 2022 06:50:10 GMT

Hi NMOORE ,

As can be seen from the picture, the two cal1 and cal_result results are different because the values in the above cards are integers and rounded, and the two values in the table below retain two decimal places, maybe you can keep the value in the above card also two decimal places

Best Regards!

Yolo Zhu

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

Re: Measure which divides two other measures giving incorrect results

NMOORE — Fri, 16 Dec 2022 08:44:26 GMT

Thanks Yolo Zhu. I'm aware of the decimal places. My issue is what is described in the original query above, "where I'm dividing two measures and getting an incorrect result in the table rows and totals". the main result is off by £395k, and varies in the table rows.

Re: Measure which divides two other measures giving incorrect results

Anonymous — Fri, 16 Dec 2022 09:34:48 GMT

Hi NMOORE

Maybe you can try to use round() function to format the measure,

e.g Round(DIVIDE([CALC_1],[CALC_2]),2)

you can refer to the following link:

Solved: Re: Incorrect division on measures - Microsoft Power BI Community

ROUND function (DAX) - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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