Forum Discussion
Asking : How to showing selected row data (some column) based based on dynamic max value
I am looking for how to visualize the table for entire data with max value as reference and following the selected filter (by slicer)
Here my data
| Area code | Product Code | Age | Qty |
| A | Product 1 | 5 | 12 |
| A | Product 1 | 2 | 6 |
| B | Product 1 | 7 | 54 |
| C | Product 1 | 12 | 32 |
| D | Product 1 | 9 | 2 |
| A | Product 2 | 3 | 8 |
| B | Product 2 | 21 | 9 |
| B | Product 2 | 21 | 11 |
| C | Product 2 | 11 | 20 |
| B | Product 3 | 8 | 4 |
I need to find which product that has oldest age, how many the qty and how many the total qty of its product based on how i filter the area (using slicer)
This is how table need to be visualized on PBIX layout
Filter/slicer all
| Product Code | Max Age | Qty of Max Age | Total Qty of product |
| Product 1 | 12 | 32 | 106 |
| Product 2 | 21 | 20 | 48 |
| Product 3 | 8 | 4 | 4 |
Filter/slicer for A only
| Product Code | Max Age | Qty of Max Age | Total Qty of product |
| Product 1 | 5 | 12 | 18 |
| Product 2 | 3 | 8 | 8 |
|
Filter/slicer for B only
| Product Code | Max Age | Qty of Max Age | Total Qty of product |
| Product 1 | 7 | 54 | 54 |
| Product 2 | 21 | 20 | 20 |
| Product 3 | 8 | 4 | 4 |
Thank you
Hi Didik62
I think you can try Max, Sum function in value field in table visual and create a measure to achieve your goal.
Max of Age column is Age column from table calculated by Maximum function in table visual.
Total Qty of Product column is Qty column from table calculated by Sum function in table visual.
Qty of Max Age is as measure.
Qty of Max Age = CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Product Code] = MAX('Table'[Product Code])&&'Table'[Age] = MAX('Table'[Age])))Create a slicer by Area code column from table. Result is as below.
All:
Select A:
Select B:
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Didik62
I think you can try Max, Sum function in value field in table visual and create a measure to achieve your goal.
Max of Age column is Age column from table calculated by Maximum function in table visual.
Total Qty of Product column is Qty column from table calculated by Sum function in table visual.
Qty of Max Age is as measure.
Qty of Max Age = CALCULATE(SUM('Table'[Qty]),FILTER(ALL('Table'),'Table'[Product Code] = MAX('Table'[Product Code])&&'Table'[Age] = MAX('Table'[Age])))Create a slicer by Area code column from table. Result is as below.
All:
Select A:
Select B:
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you so much. it works for me.
Hi Rico. for this solution i have some problem when same product code from different area have the same Age.
How i could select the correct data after filtering by areaNew data
Area code Product Code Age Qty A Product 1 12 12 A Product 1 2 6 B Product 1 12 54 C Product 1 12 32 D Product 1 9 2 A Product 2 3 8 B Product 2 21 9 B Product 2 21 11 C Product 2 11 20 B Product 3 8 4 Filter all for Product 1
Product Code Max Age Qty of Max Age Total Qty of product Product 1 12 98 106 Filter for area A
Product Code Max Age Qty of Max Age Total Qty of product Product 1 12 12 18 Filter for area B
Product Code Max Age Qty of Max Age Total Qty of product Product 1 12 54 54 Would you help me once more.
Thank you
