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.
I see you recreated the problem to test. THANK YOU for taking the time to do that.However, you only recreated the single line of data for each line in the summarized view. If you had multiple rows behind each of the Line# rows, the formula provides the incorrect result. This is the issue.
Community ChampionJohnLap- can you share the sample data please in excel/PBI - whatever you prefer?
This Posting was "deleted" and marked as success. I reopened it.
I am posting more evidence. The problem is real.
I had to recreate the problem since I resolved it by making summary views in SQL source.
Here is the summary data. This is the actual view created in PBI and the data is below it. I used the native Group By Line#
In the data table I have included the PURE CALC to show the correct answer. The formula provided in Power BI does not work as it should.
Line# Transaction Count PriorYear.Transaction_Count Pure Calc: ((B/C)-1)*100 PBI CountTest 8 = (DIVIDE('YoYDailySalesbyStateComp'[Transaction_Count],'YoYDailySalesbyStateComp'[PriorYear.Transaction_Count],0)-1)*100 1 9013 7403 21.75 35.04 2 4529 4610 -1.76 0.28 3 7641 6530 17.01 22.77 4 362 336 7.74 51.83 5 1342 1296 3.55 3.46 6 3435 3632 -5.42 -5.37 7 1116 1045 6.79 18.90 8 652 645 1.09 1.46 Here is the detail lines that make up each of the Line #'s above.
State Transaction Count PriorYear.Transaction_Count 1 2370 1260 1 3021 3159 1 3622 2984 2 1289 1116 2 1472 1732 2 1768 1762 3 2242 1431 3 2515 2523 3 2884 2576 4 59 23 4 156 133 4 147 180 5 352 353 5 509 450 5 481 493 6 976 1020 6 1173 1291 6 1286 1321 7 270 164 7 391 407 7 455 474 8 200 203 8 248 211 8 204 231 - smpa01
Is this what you are looking for ?