Ranking models by the highest value of one of its components

[Anonymous]

REVISED - 

Hi, first time here. I have a question for the community.

I have a set of data that allow me to make a table showing Model, Component and qty (across weeks).

Every model have multiple components.

I am looking to create a table that shows the top 5 Models (based on the single highest component qty) and for each Model I want to disply the top 5 components.  

For example (consider each model have over 10 components):

Model	Part	Qty
A	1	200
A	2	30
B	5	300
B	7	250
C	8	400
C	10	300

The table I am looking for should show Top 5 Model and Top 5 Component for each model:

Model	Part	Qty
C	8	400
C	10	300
B	5	300
B	7	250
A	1	200
A	2	30

Thanks.

[v-rzhou-msft]

Hi @Anonymous 

You may try my measure, you can calculate the max QTY for each model directly.

M.QTY = 
VAR _MAXQTY =
    MAXX (
        FILTER ( ALL ( 'Table' ), 'Table'[Model] = MAX ( 'Table'[Model] ) ),
        'Table'[Qty]
    )
RETURN
    IF ( SUM ( 'Table'[Qty] ) = _MAXQTY, _MAXQTY, BLANK () )

You can download the pbix file from this link:  Ranking models by the highest value of one of its components

Best Regards,

Rico Zhou

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

[Anonymous]

@v-rzhou-msft , tks but I still can't make it work. The data set I am using behind contains the history records of multiple weeks of exctraction of the data as well as the qty of components in needs are split in multiple lines one for each weeks such qty is needed. 

I am using a matrix visual to display the table. I can now have the top 5 components (for each model) with their qty, if I just look at the total (for a selected period). If I add the weeks in the column the table show lots of empty weeks, those of the selected month are populated but the rest show nothing).

[v-rzhou-msft]

Hi @Anonymous 

I need to know the columns in your table. And is Total column a measure ,calculated column or a normal column in data model?

Could you show me your data model? And this may make it easier for me to understand your matrix visual.

And could you tell me your calculate logic in your rank column if it is a measure?

Best Regards,

Rico Zhou

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

[Anonymous]

@RicoZhou I thought to have posted a reply to your points but I can't see it, so here it is:

The columns in my table are imported from an excel file with the following columns:

Model #, Component #, Comp. description, Week impacted, Quantity. Plus some other columns but not relevant to the table I need to build.

For example:

Model	Comp #	Week impacted	Qty
B	234FB	20wk43	5,765
B	234FB	20wk44	123,520
B	234FB	20wk45	122,880
B	24R546GH	20wk42	35,804

The Total column is done automaticaly by Power BI matrix.

The Rank column is just to show what I wish to have as a result. There is no measure nor other calculation behind it.

Hope this clarify the data structure. Thanks again for the help.

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I think you want to show Top5 in top1 sums in per model and per comp.

I use calculated column to achieve your goal.

My table3:

Firstlt I calculate the sum if QTY by model and comp.

SUM PER MODEL&COMP =
SUMX (
    FILTER (
        'Table (3)',
        'Table (3)'[Model] = EARLIER ( 'Table (3)'[Model] )
            && 'Table (3)'[Comp #] = EARLIER ( 'Table (3)'[Comp #] )
    ),
    'Table (3)'[Qty]
)

Then Rank the SUM by model.

Rank = RANKX(FILTER('Table (3)','Table (3)'[Model]=EARLIER('Table (3)'[Model])),'Table (3)'[SUM PER MODEL&COMP],,DESC,Dense)

Finally let Top N show 1, and others show 0.

Top N = 
var _T = SUMMARIZE(FILTER('Table (3)','Table (3)'[Rank]=1),'Table (3)'[Model],'Table (3)'[SUM PER MODEL&COMP])
Return
VAR _T2 = ADDCOLUMNS(_T,"Rank2",RANKX(_T,[SUM PER MODEL&COMP],,DESC))
Return
VAR _Model = SUMMARIZE(FILTER(_T2,[Rank2]<2),[Model])
Return
IF('Table (3)'[Model] in _Model,1,0)

Due to I only have two model, so I show Top1.

Build a matrix visual and drag TopN column and Rank into Filter Field, and Both of them show values =1.

Result:

You can download the pbix file from this link: Classification of models by the highest value of one of its components

Best Regards,

Rico Zhou

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

[vanessafvg]

you can create a rank

ModelRank = CALCULATE (RANKX(ALLEXCEPT(ModelRank,ModelRank[Model]), CALCULATE(sum(ModelRank[Qty]))))

then set the filter for that rank to 1 on the visual

[Anonymous]

Thanks for the advice, I created the rank, as per your suggestion, but it doesn't sort the table.  There is also the issue of the weeks columns. I am using a matrix table with Model, Components, Supplier etc. in the lines and weeks in columns with qty as the values.

[mpicca13]

@Anonymous @ Here's an article that shows you how to do this

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

I also recommend watching and reading content posted from these guys. You'll learn alot!

[Anonymous]

@mpicca13  Thanks, not sure I posted the reply properly or not.. noooooob... anyway thanks for the link. I did managed with it to sort and limit the component list to the top 5. Now the difficult part is: How can I further sort the shorten table by model (using as ranking the rank of the highest qty of components of each model against eachother). Something like this: