Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a sample dataset like this
month | salesperson | Iphone this year | Iphone last year | Ipad this year | Ipad last year |
Aug | bob | 20 | 15 | 30 | 25 |
Aug | evan | 40 | 35 | 50 | 45 |
I need to create a table or matrix like this
Item | total sale Aug this year | sales change (this year vs last year) |
Iphone | 60 | (20+40)-(15+35) |
Ipad | 80 | (30+50) - (25+45) |
is this possible? Thank you!
Solved! Go to Solution.
First step is to bring the data into a usable format.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNV9JRSspPApJGBkDC0BRIGINYRqZKsTowFalliXlAygQkYQxSYgpimQCVxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, salesperson = _t, #"Iphone this year" = _t, #"Iphone last year" = _t, #"Ipad this year" = _t, #"Ipad last year" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"month", "salesperson"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Product"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","this year","2021",Replacer.ReplaceText,{"Attribute.2"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","last year","2020",Replacer.ReplaceText,{"Attribute.2"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Date", each Date.FromText([month] & " 1 " & [Attribute.2])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"salesperson", "Product", "Value", "Date"})
in
#"Removed Other Columns"
yields
That's something you can load into Power BI.
If you want to show the change you usually subtract the previous value from the current value. Your example seems to indicate that you want to calculate it the other way round?
Hi,
In the raw data, you should not have this year and last year. Instead you should have year or a date as a heading. This will allow us to use the Date Intellignece functions.
First step is to bring the data into a usable format.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcixNV9JRSspPApJGBkDC0BRIGINYRqZKsTowFalliXlAygQkYQxSYgpimQCVxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [month = _t, salesperson = _t, #"Iphone this year" = _t, #"Iphone last year" = _t, #"Ipad this year" = _t, #"Ipad last year" = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"month", "salesperson"}, "Attribute", "Value"),
#"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", Currency.Type}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}),
#"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.1", "Product"}}),
#"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","this year","2021",Replacer.ReplaceText,{"Attribute.2"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","last year","2020",Replacer.ReplaceText,{"Attribute.2"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value1", "Date", each Date.FromText([month] & " 1 " & [Attribute.2])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"salesperson", "Product", "Value", "Date"})
in
#"Removed Other Columns"
yields
That's something you can load into Power BI.
If you want to show the change you usually subtract the previous value from the current value. Your example seems to indicate that you want to calculate it the other way round?
Thank you very much!
User | Count |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |