Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
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 ) )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |