Forum Discussion
Solved
ON ADD COLUMN: DIVIDE() function with two columns yields wrong result?
I am new! I am probably missing something obvious!!! But after 12 hours of trying to figure this out I need help. Newest version Power BI Desktop. Just want to Divide two numeric columns. When I ...
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
Community Champion
Community ChampionOkay 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.