Forum Discussion
calculate sum distinct values based on another column
Hi,
I'm try to calculate Project value based on ID column distinct value. I have a table with columns It looks like this:
| Project category | Sub Category | Project Value | Plant | Status | Efforts | ID |
| CTW | Packaging | 180000 | Columbus | On Hold | 0.00 | 1 |
| CTW | Packaging | 180000 | Columbus | On Hold | 0.00 | 1 |
| CTW | Packaging | 54000 | Drogheda | On Track | 0.10 | 2 |
| CTW | Packaging | 54000 | Drogheda | On Track | 0.10 | 2 |
| CTW | Packaging | 50000 | Curtiba | On Track | 0.30 | 3 |
| CTW | Packaging | 50000 | Curtiba | On Track | 0.30 | 3 |
| CTW | Packaging | 26000 | JDF | On Track | 0.15 | 4 |
| CTW | Packaging | 26000 | JDF | On Track | 0.15 | 4 |
| CTW | Packaging | 55000 | Columbus | On Track | 0.35 | 5 |
| CTW | Packaging | 55000 | Columbus | On Track | 0.25 | 5 |
| CTW | Packaging | 55000 | Columbus | On Track | 0.10 | 5 |
| CTW | Product | 30000 | Tuas | On Track | 0.60 | 6 |
| CTW | Product | 30000 | Tuas | On Track | 0.60 | 6 |
i want sum of Project value using DIstinct value of ID , i have ID duplicate values but i want to find (ID column) unique value based Project values
i tried measure , it's not working if i using Group by means it's work but i need other columns
Total sales = SUMX(DISTINCT('table'[Id]),MAX('table'[Project Value]))
Any help there? I'm was searching through community posts but nothing worked for me
Anonymous , Try like
Total sales = SUMX(values('table'[Id]),calculate(MAX('table'[Project Value])))
3 Replies
- amitchandak
Super User
Anonymous , Try like
Total sales = SUMX(values('table'[Id]),calculate(MAX('table'[Project Value])))
- Jihwan_Kim
Total sales : =
SUMX (
DISTINCT ( 'table'[Id] ),
CALCULATE ( DISTINCT ( 'Table'[Project Value] ) )
)
