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

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

Didik62 — Wed, 06 Oct 2021 05:12:33 GMT

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

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

Anonymous — Fri, 08 Oct 2021 03:02:39 GMT

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.

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

Didik62 — Tue, 12 Oct 2021 06:25:50 GMT

Thank you so much. it works for me.

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

Didik62 — Tue, 26 Oct 2021 10:45:12 GMT

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