Finding top product model across each region by product category

[RobinNandal]

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

[Thejeswar · Accepted 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!!!

[RobinNandal]

@Thejeswar This worked perfectly. Can't thank you enough 🙂

Robin

[v-frfei-msft]

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

[RobinNandal]

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

[Thejeswar]

@RobinNandal,

My Solution answers your need....Do check that out...!!

[RobinNandal]

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. 🙂