Forum Discussion
Dynamically return single value by columns
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
Hi all,
I created a new table with unique item No to return a single value (e.g. 3000) and not sum by Details.
4 Replies
Not clear if you need a sum of averages or something else. Please provide sanitized sample data that fully covers your issue.
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.Hi and thanks for the reply. I found a solution by creating a new table with unique item no. Havent tried your 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.
