Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to find ‘top model’ across regions across product category in only last 3 years.
My input data looks something like this:
Machine ID | Model | Product | Ship Year | Region |
1001 | ABC | Truck | 2001 | North America |
1002 | XYZ | Crane | 2015 | Asia |
1003 | MNO | Trolley | 2016 | Africa |
1004 | ABC | Truck | 2017 | South America |
1005 | MNO | Trolley | 2018 | North America |
… | … | … | … | … |
And I am looking for an output like this in vizualization:
Region | Truck | Crane | Trolley |
North America | xyz | abc | mno |
South America |
|
|
|
Asia |
|
|
|
Africa |
|
|
|
I first calculate units shipped by simple counting the Ship Year column: ShippedUnits = Count(Ship Year)
I tried solving this by filter. But issue is Power BI won’t take two filters with Top N type of filtering. Also, the output in matrix type visualization, model (text) is shown either as FIRST or LAST or Count which does not work.
I next tried the LOOKUPValue and in expression used MAX(ShippedUnits)
This seemed to work but as soon as I selected all the Regions, I kept getting error.
Please help
Solved! Go to Solution.
Hi @RobinNandal,
Had a hard time finding out the proper method to rank the values, but finally achieved
Due to multiple steps, I am just giving the Final M-Query for the report
Pivot the Table using the below M-Query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZMxD4IwEIX/iuns0DuB4ogYN3HAQSUMhGAkoiQog//eAokRvDPt0ob25e7e+0qSCJASxFwEq1Cv+6bNr3rH4TCqm+dlFtyKpswzkc57NeqLw/Gk17DJ7kWvBrcr8Sg/ooX+3ka7vmRdVcVrkHmd7PxdzSF6g9JbXLe/vV26rM/N6lHOXE6tKGdq4sznRxg7W1LOfMYZSDtnYMUMTJiBKTOwYgaWzPp+01lZZsAyI9Wm8MAKHkpqCM4gUujY+BDt4sOOYrzemMWHDqGePnn8Q3CUGlK/G5+aonoTvtI3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Machine ID" = _t, Model = _t, Product = _t, #"Ship Year" = _t, Region = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine ID", Int64.Type}, {"Model", type text}, {"Product", type text}, {"Ship Year", Int64.Type}, {"Region", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Region", "Model"}, {{"all data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}), #"Expanded all data" = Table.ExpandTableColumn(#"Grouped Rows", "all data", {"Product", "Index"}, {"all data.Product", "all data.Index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded all data",{{"all data.Index", "Partition.Index"}, {"all data.Product", "Partition.Product"}}) in #"Renamed Columns"
Summarize the Output of the Above Query using the below DAX
Summary_table2 = SUMMARIZE(Table2,Table2[Region],Table2[Partition.Product],Table2[Model],"Top Model",MAX(Table2[Partition.Index]))
Then Create a calculated column for finding the RANK using the below DAX
Rank1 = RANKX(FILTER(FILTER(Summary_table2, Summary_table2[Region]=EARLIER(Summary_table2[Region])),Summary_table2[Partition.Product]=EARLIER(Summary_table2[Partition.Product])), Summary_table2[Top Model],,DESC)
Your Summary table data will look like the one below
Now put the data in the Matrix Visual and Filter it by Rank = 1
The Final matrix will look like
Note: I have included some more records to the dataset that you gave to give a clear understanding
Click here for the PBIX File
The PBIX File also has one more way of achieving this solution. Table 1 and Table 2 give the same output but in different methods. I have given the M-Query of Table 2 in this post
Hope this helps!!!
Hi @RobinNandal,
Had a hard time finding out the proper method to rank the values, but finally achieved
Due to multiple steps, I am just giving the Final M-Query for the report
Pivot the Table using the below M-Query
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZMxD4IwEIX/iuns0DuB4ogYN3HAQSUMhGAkoiQog//eAokRvDPt0ob25e7e+0qSCJASxFwEq1Cv+6bNr3rH4TCqm+dlFtyKpswzkc57NeqLw/Gk17DJ7kWvBrcr8Sg/ooX+3ka7vmRdVcVrkHmd7PxdzSF6g9JbXLe/vV26rM/N6lHOXE6tKGdq4sznRxg7W1LOfMYZSDtnYMUMTJiBKTOwYgaWzPp+01lZZsAyI9Wm8MAKHkpqCM4gUujY+BDt4sOOYrzemMWHDqGePnn8Q3CUGlK/G5+aonoTvtI3", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Machine ID" = _t, Model = _t, Product = _t, #"Ship Year" = _t, Region = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Machine ID", Int64.Type}, {"Model", type text}, {"Product", type text}, {"Ship Year", Int64.Type}, {"Region", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Region", "Model"}, {{"all data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}), #"Expanded all data" = Table.ExpandTableColumn(#"Grouped Rows", "all data", {"Product", "Index"}, {"all data.Product", "all data.Index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded all data",{{"all data.Index", "Partition.Index"}, {"all data.Product", "Partition.Product"}}) in #"Renamed Columns"
Summarize the Output of the Above Query using the below DAX
Summary_table2 = SUMMARIZE(Table2,Table2[Region],Table2[Partition.Product],Table2[Model],"Top Model",MAX(Table2[Partition.Index]))
Then Create a calculated column for finding the RANK using the below DAX
Rank1 = RANKX(FILTER(FILTER(Summary_table2, Summary_table2[Region]=EARLIER(Summary_table2[Region])),Summary_table2[Partition.Product]=EARLIER(Summary_table2[Partition.Product])), Summary_table2[Top Model],,DESC)
Your Summary table data will look like the one below
Now put the data in the Matrix Visual and Filter it by Rank = 1
The Final matrix will look like
Note: I have included some more records to the dataset that you gave to give a clear understanding
Click here for the PBIX File
The PBIX File also has one more way of achieving this solution. Table 1 and Table 2 give the same output but in different methods. I have given the M-Query of Table 2 in this post
Hope this helps!!!
Hi @RobinNandal,
Based on my test, we can take the following steps to meet your requirement.
1.Enter the data and create the calculated columns as below.
Column = IF(Table1[Ship Year]>=2016,1,0)
Column 2 = IF(Table1[Ship Year]>=2016,CALCULATE(SUM(Table1[Column]),FILTER(ALL(Table1),Table1[Product]=EARLIER(Table1[Product])&& Table1[Region]=EARLIER(Table1[Region]))),BLANK())
result = var maxs = CALCULATE(SUM(Table1[Column]),FILTER(ALL(Table1),Table1[Product]=EARLIER(Table1[Product])&& Table1[Region]=EARLIER(Table1[Region]))) var maxall = CALCULATE(MAX(Table1[Column 2]),FILTER(ALL(Table1),Table1[Product] = EARLIER(Table1[Product]))) return IF(Table1[Ship Year]>=2016 && maxall=maxs, Table1[Model])
2. Put the result column to the metrix visual, then we can get the result as we excepted.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/l1pdjezw3gcczg8/Finding%20top.pbix?dl=0
Regards,
Frank
HI Frank,
Thank you for the solution. This is working.
Going further on this, in case I want to show top model for each region in respective product category?
Right now, it shows only the regions which have top model in each product category.
Thank you
Robin
HI Thejeswar,
My work laptop is not allowing me to download your file due to some access restrictions. I will definitely check it but can do so only on my personal laptop.
Waiting to do so tonight. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |