Forum Discussion
Sum distinct value based on another column
Hello,
My table follows this structure:
| Dimen | Level | Quantity |
| John | XXX | 15 |
| Alice | XXX | 15 |
| Lexa | XXX | 15 |
| Vini | YYY | 15 |
| Skuerbis | YYY | 15 |
| Kim | ZZZ | 5 |
As you can see, the value of the column Quantity is always repeated for each Level. I would like to create a measure that sums only the maximum (or distinct) value of the Quantity column for each Level.
For example:
for the example above, the total sum should be: 35 (15 + 15 + 5)
Can someone help me?
Hi, Anonymous
Please check the below.
Total Qty by distinct Level =
VAR grouptable =
GROUPBY (
'Table',
'Table'[Level ],
"@maxqty", MAXX ( CURRENTGROUP (), 'Table'[ Quantity] )
)
RETURN
SUMX ( grouptable, [@maxqty] )Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/
1 Reply
Hi, Anonymous
Please check the below.
Total Qty by distinct Level =
VAR grouptable =
GROUPBY (
'Table',
'Table'[Level ],
"@maxqty", MAXX ( CURRENTGROUP (), 'Table'[ Quantity] )
)
RETURN
SUMX ( grouptable, [@maxqty] )Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.
Linkedin: https://www.linkedin.com/in/jihwankim1975/