Forum Discussion
Rank Based on Multiple Columns Coming From Multiple Tables
Hi Friends,
I am troubling in filtering table result based on mutiple columns which are coming from multiple tables. For an exmaple below is the sample matrix visual.
| Product Name | Plant | Customer | Short qty |
| A | 1 | C1 | 455 |
| A | 2 | C1 | 343 |
| B | 3 | C2 | 545 |
| B | 4 | C3 | 534 |
| C | 1 | C1 | 665 |
| C | 2 | C3 | 332 |
| D | 3 | C2 | 543 |
| D | 4 | C1 | 566 |
Columns "Product Name", "Plant", "Customer" are all coming from different tables.
Now i need to filter this matrix visual based on Short Qty i.e. Top 3
| Product Name | Plant | Customer | Short qty |
| C | 1 | C1 | 665 |
| D | 4 | C1 | 566 |
| B | 3 | C2 | 545 |
Please help me here with measure or any other trick.
amitchauhan @gregdorval @amitchandak @JihwanKim @Jihwan_Kim @ChandeepChhabra @Mynda @Greg_Deckler
Hi Anonymous ,
A method, use the Top N filter type in Filter Pane to get the result you want. Please follow the steps.
1 Drag the field [Short qty] to the filter pane, choose 'Top N', enter 3 after "Top" under the show items.
2 Drag the [Short qty] again to the box under "By value", select "sum" or other aggregation you want via the arrow icon.
Refer this screenshot.
Result:
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Anonymous , create rank like
rankx(summarize(allselected(Table), Table[Product Name],Table[Plant],Table[Customer]), calculate(sum(Table[Short Qty])),,desc,dense)
and use the visual level filter of rank <=3
- Jihwan_Kim
Hi,
I am not sure how your data mode looks like, but please check the below picture and the attached pbix file.
Qty total: = SUM ( Data[Qty] )Qty top3: = CALCULATE ( [Qty total:], KEEPFILTERS ( TOPN ( 3, ALL ( Data ), [Qty total:], DESC ) ) ) - v-chenwuz-msft
Community Support
Hi Anonymous ,
A method, use the Top N filter type in Filter Pane to get the result you want. Please follow the steps.
1 Drag the field [Short qty] to the filter pane, choose 'Top N', enter 3 after "Top" under the show items.
2 Drag the [Short qty] again to the box under "By value", select "sum" or other aggregation you want via the arrow icon.
Refer this screenshot.
Result:
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
