Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Power Query
- Re: Price Variance by Category & Year

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Price Variance by Category & Year

06-09-2020
08:48 PM

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 Color | Secondary Color | Item | Year | Price |

Blue | Light Blue | A | 2019 | 11 |

Blue | Light Blue | A | 2020 | 13 |

Blue | Dark Blue | B | 2018 | 200 |

Blue | Dark Blue | B | 2019 | 201 |

Red | Light Red | C | 2019 | 55 |

Red | Light Red | C | 2020 | 50 |

Red | Light Red | C | 2020 | 61 |

Grey | Grey | D | 2019 | 1 |

Orange | Orange | E | 2020 | 40 |

While | While | F | 2018 | 90 |

Result:

Primary Color | Secondary Color | Item | 2018 | 2019 | 2020 | 2018 vs 2019 | 2019 vs 2020 |

Blue | Light Blue | A | 11 | 13 | 18.2% | ||

Blue | Dark Blue | B | 200 | 201 | 0.5% | ||

Red | Light Red | C | 55 | 61 | 10.9% |

Thank you.

Regards,

Zi

Solved! Go to Solution.

4 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2020
12:35 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2020
06:47 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2020
09:49 AM

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.

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2020
06:47 AM

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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2020
09:49 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-19-2020
12:35 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Announcements

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

Top Solution Authors

User | Count |
---|---|

63 | |

33 | |

24 | |

16 | |

9 |

Top Kudoed Authors

User | Count |
---|---|

69 | |

43 | |

38 | |

26 | |

23 |