Forum Discussion
ON ADD COLUMN: DIVIDE() function with two columns yields wrong result?
Correction: The real solution was a little more complex
Transaction Var % Measure = (DIVIDE(CALCULATE(SUM('Table'[Count])),CALCULATE(SUM('Table'[PriorYear.Count])),0)-1)*100
This actually solves the problem.
Converting to a measure is required and will not work against a Direct Query.
JohnLap,
Your DAX formula is correct. Do you use the latest version of Power BI Desktop(2.53.4954.621)? Which table does the Line field come from? Is it also a field in the YoY_Daily_Sales table?
Regards,
Lydia
Thank you for the response.
I think I may have figured out what is happening, but I am surprised if it is designed this way.
The Line# comes from the same table. It is actually STATE but I changed it to Line # for simplicity.
While the table view in PowerBI is summarized at the Line# level (Group By) there are multiple rows behind that. It appears that Power BI is doing the division on each line of the lowest level of detail and then averaging or summing those. This is absolutely the wrong way to do it. I would have assumed that "Row by Row" calculations meant each Row that is in the VIEW. To solve the problem, I had to create a SQL view in the source table that summarized the data by Line#(State). So, that becomes the lowest level of detail.
Either the documentation, or this DIVIDE function needs to be changed. Users of these reports can easily do the division on the view they see and determine that the math is just wrong. Great way to lose credibility of a report. Also, dramatically reduces the "POWER" in PowerBI if I have to create views to aggregate to every group by level I would want in the report. My expectation was that I could produce the data at the lowest level and have all of the math work out as I grouped as I pleased in Power BI. NOPE!