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.
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 |
smpa01Community Champion
Is 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.
- smpa01
Okay this is what I did
let Source = Web.Page(Web.Contents("http://community.powerbi.com/t5/Desktop/ON-ADD-COLUMN-DIVIDE-function-with-two-columns-yields-wrong/m-p/330494#M147703")), Data2 = Source{2}[Data], #"Promoted Headers" = Table.PromoteHeaders(Data2, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"State", Int64.Type}, {"Transaction Count", Int64.Type}, {"PriorYear.Transaction_Count", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"State"}, {{"Transaction Count", each List.Sum([Transaction Count]), type number}, {"Prior Year.Transaction Count", each List.Sum([PriorYear.Transaction_Count]), type number}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each (([Transaction Count]/[Prior Year.Transaction Count])-1)*100), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type number}}), #"Rounded Off" = Table.TransformColumns(#"Changed Type1",{{"Custom", each Number.Round(_, 2), type number}}) in #"Rounded Off"is
I will go apply those steps now and see what I get.
Thank you very much for the support!!!
I will report back after lunch.