Forum Discussion
Can PowerBI do this?
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!
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?
3 Replies
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.
