I have a large data set with columns that contain client, sales rep, customer, month, quarter, 2019 sales, 2020 sales and sales difference. I have added a column to calculate the percentage difference by dividing the sales difference and 2019 sales. When I save and load data to Pivot Table, all my sales total correctly with the exception of my percentage difference. Instead of calculating the percentage difference based on the row, it adding them.
I am very familiar with adding a Calculated Field in Pivot Tables to calculate the percentage accurately. The Calculated Field is grayed out when using Power Query. How can I get the correct percentage to show up on my report?
For example -
2019 sales: $17,284
2020 sales $12,544
Dollar variance: ($4,740)
Percentage variance: -2428%
The actual percentage variance should be -27% (-$4,740/17,284) for this specific row.
Advanced Editor:
let
Source = Excel.Workbook(File.Contents("C:\Users\abucci\Dropbox (Avision Sales Group)\Client Services\2 Client Support\2 Client Administration (Alicia)\Reports\RSM & TM Sales Repts\T3 Sales Report.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
#"Appended Query" = Table.Combine({#"Promoted Headers", #"T4 Sales", #"T7 Sales", #"T8 Sales"}),
#"Filtered Rows" = Table.SelectRows(#"Appended Query", each true),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"2019 Sum", Currency.Type}, {"2020 Sum", Currency.Type}, {"Variance", Currency.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "% Variance", each [Variance]/[2019 Sum]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Assigned To", "Related Account", "Client", "Month", "Quarter", "2019 Sum", "2020 Sum", "Variance", "% Variance", "Status", "Ter"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"% Variance", Percentage.Type}})
in
#"Changed Type1"
