Can PowerBI do this?

[Anonymous]

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!

[Ashish_Mathur]

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.

[lbendlin · Accepted 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?

[Anonymous]

Thank you very much!