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.
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 ?
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.
- 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.