Asking : How to showing selected row data (some column) based based on dynamic max value

[Didik62]

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

[v-rzhou-msft · Accepted solution]

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 Zhou

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

[Didik62]

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 area

New 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

[Didik62]

Thank you so much. it works for me.