cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

Ranking models by the highest value of one of its components

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):

ModelPartQty
A1200
A230
B5300
B7250
C8400
C10300

 

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

ModelPartQty
C8400
C10300
B5300
B7250
A1200
A230

 

Thanks.

1 ACCEPTED SOLUTION

Hi @Renato28 

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:

1.png

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:

3.png

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. 

 

View solution in original post

10 REPLIES 10
Community Support
Community Support

Hi @Renato28 

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. 

@RicoZhou , 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).

Hi @Renato28 

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. 

 

 

 

@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:

ModelComp #Week impactedQty
B234FB20wk435,765
B234FB20wk44123,520
B234FB20wk45122,880
B24R546GH20wk4235,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.

Hi @Renato28 

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

Hi @Renato28 

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:

1.png

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:

3.png

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. 

 

View solution in original post

Super User I
Super User I

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
 
Capture.PNG
 
 




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.

@Renato28 @ 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!

 

@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:Table sorted.PNG

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