Forum Discussion
Post PartisanHelp with simple Division Measure producing incorrect result
I have a simple dashboard where I'm trying to calculate the delta between my Sessions_Clicks_% and my Sessions_Clicks_%_Baseline.
The Sessions_Clicks_%_Delta should be = (Sessions_Clicks_% - Sessions_Clicks_%_Baseline)/(Sessions_Clicks_%_Baseline)
Sessions_Clicks_% = 'Sheet1'[SUM_Web_Clicks]/'Sheet1'[SUM_Web_Sessions]
Sessions_Clicks_%_Baseline =
CALCULATE(
'Sheet1'[Sessions_Clicks_%],
FILTER(
ALL ('Calendar'),
'Calendar'[Year] = 2025
&& MONTH ('Calendar'[Date]) IN {1}
)
)
Sessions_Clicks_%_Delta = ('Sheet1'[Sessions_Clicks_%]-'Sheet1'[Sessions_Clicks_%_Baseline])/'Sheet1'[Sessions_Clicks_%_Baseline]
For some reason Sessions_Clicks_%_Delta is off slightly. I've exported the data from the table visual and calculated the expected result in Excel.
My sample file is attached.
lbendlin Ashish_Mathur
I have determined the issue is due to rounding and the number of decimal places. As you can see below, Ive taken two seperate table exports from the same Power BI table. In the first export I had the measures in PBI formatted to two decimal places. This results in slightly inccorrect results as you can see in column J (comparing the PBI measure in column H with the same Excel calculation in column I). In the second export I had the measures in PBI formatted to four decimal places and the results are correct (column J shows zero difference when comparing columns H & I).
The math is correct when using four decimal places. When displaying two decimal places, the values in column F & H are rounded, however the calculation in column H is done with four decimal places in columns F & G.Power BI takes into consideration the decimal places that are not visible so simply taking an export (which exports just what is visible in the visual) can result to a misleading calculation in Excel.
9 Replies
- lbendlin
Super User
Your numbers are accurate, the expectations are wrong.
Please use DIVIDE for divisions, and use variables.
Try not to nest measures too much as each of them comes with an implicit CALCULATE.
You also were using the wrong date field and not marking the calendar as Date table.
Attached a cleaned up version.
ERingI don't understand how the expectations are wrong. I took an export from the file you provided and you can see that the calculation in column I produces slightly different results from the Power BI measure result in column H. It's very close, but if a user were to do the calcuation, they will find a different result from the Power BI measure result.
- lbendlin
You are calculating percentage differences of two percentages, not of regular values.
- Ashish_Mathur
Hi,
Those numbers are correct. Have made some changs to the file.
- ERing
lbendlin Ashish_Mathur
I have determined the issue is due to rounding and the number of decimal places. As you can see below, Ive taken two seperate table exports from the same Power BI table. In the first export I had the measures in PBI formatted to two decimal places. This results in slightly inccorrect results as you can see in column J (comparing the PBI measure in column H with the same Excel calculation in column I). In the second export I had the measures in PBI formatted to four decimal places and the results are correct (column J shows zero difference when comparing columns H & I).
The math is correct when using four decimal places. When displaying two decimal places, the values in column F & H are rounded, however the calculation in column H is done with four decimal places in columns F & G.- danextian
Power BI takes into consideration the decimal places that are not visible so simply taking an export (which exports just what is visible in the visual) can result to a misleading calculation in Excel.
v-echaithraCommunity Support
Hi ERing ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E. - v-echaithra
Hi ERing ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E. - v-echaithra
Hi @ERing ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra E.
