Dynamically return single value by columns

[MIkkelHyldig]

Hi, 

The end result should be: Item no 32 = 3000 quantity and not 9000 when I sum on Item No. 

The issue is that my table duplicates the quantity by the "Details" as quantity only relates to Doc and Item no level. 

Doc No	Item No	Details	Quantity	Date	batch ID
2	32	1	3000	27-01-21	1
2	32	2	3000	27-01-21	1
2	32	3	3000	27-01-21	1

I need a dynamically measure, so I can filter by details 1, 2 or 3 and still get quantity 3000. 

Thanks, 

Mikkel 

[MIkkelHyldig · Accepted solution]

Hi all, 
I created a new table with unique item No to return a single value (e.g. 3000) and not sum by Details. 

[v-angzheng-msft]

Hi, @MIkkelHyldig 

Try to create a measure like this:

Measure = 
CALCULATE(
    // MIN('Table'[Quantity]),
    // MAX('Table'[Quantity]),
    AVERAGE('Table'[Quantity]),
    FILTER(ALLSELECTED('Table'),'Table'[Doc No]=MAX('Table'[Doc No])&&'Table'[Item No]=MAX('Table'[Item No]))
    )

Result:

Best Regards,
Community Support Team _ Zeon Zheng

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

[MIkkelHyldig]

Hi and thanks for the reply. I found a solution by creating a new table with unique item no. Havent tried your solution 

[lbendlin]

Not clear if you need a sum of averages or something else.  Please provide sanitized sample data that fully covers your issue.