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 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 |
Community ChampionIs this what you are looking for ?
No. The formulas work fine at the lowest level of detail record for the source data. In other words, if it is not a summary of the source data then the formula works. If it is summarized, in this example, at the Line # level it does not.
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.
Hi JohnLap,
I am having same issue where division is going wrong as my data is also summarized.Can you please share how did u solve this in SQL view.
Thanks..