cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Price Variance by Category & Year

Hello,

 

I need to calculate price variance of duplicate items (eg: Item A, B, and C):

  • By Primary Color,  Secondary Color, and Year
  • And use Max Value only
Primary ColorSecondary ColorItemYearPrice
BlueLight BlueA201911
BlueLight BlueA202013
BlueDark BlueB2018200
BlueDark BlueB2019201
RedLight RedC201955
RedLight RedC202050
RedLight RedC202061
GreyGreyD20191
OrangeOrangeE202040
WhileWhileF201890

 

Result:

Primary ColorSecondary ColorItem2018201920202018 vs 20192019 vs 2020
BlueLight BlueA 1113 18.2%
BlueDark BlueB200201 0.5% 
RedLight RedC 5561 10.9%

 

 

Thank you.

 

Regards,

Zi

4 ACCEPTED SOLUTIONS
Super User II
Super User II

Hi @zileng 

 

Please see the attached file with a DAX solution, this can be done in Power Query but it's better to calculate it using DAX.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

Community Support
Community Support

Hi @zileng ,

You could refer to @Mariusz 's suggestions or try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Price", List.Max),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "2018 vs 2019", each [2018]/[2019]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "2019vs 20202", each [2019]/[2020]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Item] = "A" or [Item] = "B" or [Item] = "C"))
in
    #"Filtered Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User I
Super User I

I had some fun doing it dynamically in a query, but as @Mariusz said, it's easier in dax.

Here it is:

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    
    #"Max Price per Year" = Table.Group(#"Changed Type", {"Primary Color", "Secondary Color", "Item", "Year"}, {{"Price", each List.Max([Price]), type number}}),
    #"Find Comparable" = Table.Group(#"Max Price per Year", {"Primary Color", "Secondary Color", "Item"}, {{"c", each Table.RowCount(_), Int64.Type}, {"A", each _, type table [Primary Color=text, Secondary Color=text, Item=text, Year=text, Price=number]}}),
    #"Filtered Comparable" = Table.SelectRows(#"Find Comparable", each ([c] > 1)),
    #"Remove Count" = Table.RemoveColumns(#"Filtered Comparable",{"c"}),
    #"Expanded Year Price" = Table.ExpandTableColumn(#"Remove Count", "A", {"Year", "Price"}, {"Year", "Price"}),
    
    YearList = List.Sort(List.Distinct(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB")[Year])),
    #"Pivoted Years" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB"), YearList, "Year", "Price"),

    Comparisons = (l as list, n as number, optional out as text, optional pstep as text) =>
        let
            k = n - List.Count(l),
            newouts =
                if k= 0
                    then "let " & "Comp"&Text.From(k+1)&" = Table.AddColumn(#"&"""t"""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                else
                    if k+1<n
                        then out & ",Comp"&Text.From(k+1)&" = Table.AddColumn(#"&""""&pstep&""""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                    else out,
            Rest =
                if k+1<n
                    then @Comparisons(List.RemoveFirstN(l, 1), n, newouts, "Comp"&Text.From(k+1))
                else out & " in Comp"&Text.From(k)
        in Rest,
    RecBuild = (t as table) => [t = t, Table.AddColumn=Table.AddColumn, Value.Divide = Value.Divide, Percentage.Type = Percentage.Type],


    AddComparisons = Expression.Evaluate(Comparisons(YearList, List.Count(YearList)), RecBuild(#"Pivoted Years"))
in
    AddComparisons

 

 

 


Explanations:

From #"Max Price per Year" to #"Expanded Year Price" we get only data comparable, and the corresponding max for each year.


YearList helps us in Pivoting the years as columns.

Comparisons is a function that returns a query in text, with YearCount-1 steps, one for each comparison.


RecBuild is an easy function to build a record so we won't have to write a lot of things if we're calling it.


AddComparisons calls for the evaluation of Comparisons using RecBuild.


voila!


image.pngimage.pngimage.png




Feel free to connect with me:
LinkedIn

View solution in original post

I had more fun.

Skipped some steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", type text}, {"Price", Int64.Type}}),
    YearList = List.Sort(List.Distinct(#"Changed Type"[Year])),
    #"Pivoted Years" = Table.Pivot(#"Changed Type", YearList, "Year", "Price", List.Max),

    Comparisons = List.RemoveNulls(List.Generate(() => [x = YearList, k = 0, y = null], each [k] < List.Count([x]), each [x=[x], k = [k]+1, y = [x]{[k]} & " vs " & [x]{[k]+1}], each [y])),
    GroupList = List.Transform(Comparisons, each {_, Expression.Evaluate( "each Value.Divide(List.Sum(["&Text.AfterDelimiter(_, "vs ")&"]), List.Sum(["&Text.BeforeDelimiter(_, " ")&"]))-1", [List.Sum = List.Sum, Value.Divide  = Value.Divide, Table.RowCount = Table.RowCount]), Percentage.Type}),
    #"Grouped Rows" = Table.Group(#"Pivoted Years", Table.ColumnNames(#"Pivoted Years"), GroupList)
in
    #"Grouped Rows"

 

 

All groupings before pivoting disappears --> this gives us all values, even non comparable ones

Now Comparisons generates a list of all possible comparisons

GroupList is the list read in the fake #"Grouped Rows" for the new columns.




Feel free to connect with me:
LinkedIn

View solution in original post

4 REPLIES 4
Super User I
Super User I

I had some fun doing it dynamically in a query, but as @Mariusz said, it's easier in dax.

Here it is:

 

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    
    #"Max Price per Year" = Table.Group(#"Changed Type", {"Primary Color", "Secondary Color", "Item", "Year"}, {{"Price", each List.Max([Price]), type number}}),
    #"Find Comparable" = Table.Group(#"Max Price per Year", {"Primary Color", "Secondary Color", "Item"}, {{"c", each Table.RowCount(_), Int64.Type}, {"A", each _, type table [Primary Color=text, Secondary Color=text, Item=text, Year=text, Price=number]}}),
    #"Filtered Comparable" = Table.SelectRows(#"Find Comparable", each ([c] > 1)),
    #"Remove Count" = Table.RemoveColumns(#"Filtered Comparable",{"c"}),
    #"Expanded Year Price" = Table.ExpandTableColumn(#"Remove Count", "A", {"Year", "Price"}, {"Year", "Price"}),
    
    YearList = List.Sort(List.Distinct(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB")[Year])),
    #"Pivoted Years" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Year Price", {{"Year", type text}}, "en-GB"), YearList, "Year", "Price"),

    Comparisons = (l as list, n as number, optional out as text, optional pstep as text) =>
        let
            k = n - List.Count(l),
            newouts =
                if k= 0
                    then "let " & "Comp"&Text.From(k+1)&" = Table.AddColumn(#"&"""t"""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                else
                    if k+1<n
                        then out & ",Comp"&Text.From(k+1)&" = Table.AddColumn(#"&""""&pstep&""""&", "&""""&l{0}&" vs "&l{1}&""""&", each Value.Divide(["&l{1}&"], ["&l{0}&"])-1, Percentage.Type)"
                    else out,
            Rest =
                if k+1<n
                    then @Comparisons(List.RemoveFirstN(l, 1), n, newouts, "Comp"&Text.From(k+1))
                else out & " in Comp"&Text.From(k)
        in Rest,
    RecBuild = (t as table) => [t = t, Table.AddColumn=Table.AddColumn, Value.Divide = Value.Divide, Percentage.Type = Percentage.Type],


    AddComparisons = Expression.Evaluate(Comparisons(YearList, List.Count(YearList)), RecBuild(#"Pivoted Years"))
in
    AddComparisons

 

 

 


Explanations:

From #"Max Price per Year" to #"Expanded Year Price" we get only data comparable, and the corresponding max for each year.


YearList helps us in Pivoting the years as columns.

Comparisons is a function that returns a query in text, with YearCount-1 steps, one for each comparison.


RecBuild is an easy function to build a record so we won't have to write a lot of things if we're calling it.


AddComparisons calls for the evaluation of Comparisons using RecBuild.


voila!


image.pngimage.pngimage.png




Feel free to connect with me:
LinkedIn

View solution in original post

I had more fun.

Skipped some steps:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", type text}, {"Price", Int64.Type}}),
    YearList = List.Sort(List.Distinct(#"Changed Type"[Year])),
    #"Pivoted Years" = Table.Pivot(#"Changed Type", YearList, "Year", "Price", List.Max),

    Comparisons = List.RemoveNulls(List.Generate(() => [x = YearList, k = 0, y = null], each [k] < List.Count([x]), each [x=[x], k = [k]+1, y = [x]{[k]} & " vs " & [x]{[k]+1}], each [y])),
    GroupList = List.Transform(Comparisons, each {_, Expression.Evaluate( "each Value.Divide(List.Sum(["&Text.AfterDelimiter(_, "vs ")&"]), List.Sum(["&Text.BeforeDelimiter(_, " ")&"]))-1", [List.Sum = List.Sum, Value.Divide  = Value.Divide, Table.RowCount = Table.RowCount]), Percentage.Type}),
    #"Grouped Rows" = Table.Group(#"Pivoted Years", Table.ColumnNames(#"Pivoted Years"), GroupList)
in
    #"Grouped Rows"

 

 

All groupings before pivoting disappears --> this gives us all values, even non comparable ones

Now Comparisons generates a list of all possible comparisons

GroupList is the list read in the fake #"Grouped Rows" for the new columns.




Feel free to connect with me:
LinkedIn

View solution in original post

Community Support
Community Support

Hi @zileng ,

You could refer to @Mariusz 's suggestions or try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUfLJTM8oUYByHIHYyMDQEkgZGirF6uBTZWQAUmWMrMolsSgbpsgJYpQFmDIgoMoSQoFVBaWmwC2EsJ0RikxN8aoBu8nUgLAaM4hd7kWplUAelHJB8j1Y2r8oMS8d5Ew4wxVhhAnEmvCMzByQDIx2Q/jbEqgiFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Primary Color" = _t, #"Secondary Color" = _t, Item = _t, Year = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Primary Color", type text}, {"Secondary Color", type text}, {"Item", type text}, {"Year", Int64.Type}, {"Price", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Year", type text}}, "en-US")[Year]), "Year", "Price", List.Max),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "2018 vs 2019", each [2018]/[2019]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "2019vs 20202", each [2019]/[2020]),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Item] = "A" or [Item] = "B" or [Item] = "C"))
in
    #"Filtered Rows"

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User II
Super User II

Hi @zileng 

 

Please see the attached file with a DAX solution, this can be done in Power Query but it's better to calculate it using DAX.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors