cancel
Showing results for
Did you mean:
Helper I

## 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

2 ACCEPTED SOLUTIONS
Super User

@ranga_88 , Try like

Total sales = SUMX(values('table'[Id]),calculate(MAX('table'[Project Value])))

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Helper I

Hi,

i have tried

Total sales = CALCULATE(SUMX('table','table'[Project Value]),MIN('table'[Id],0))

Total sales = SUMX(values('table'[Id]),calculate(MAX('table'[Project Value])))

Both are working for me thanks for your support

3 REPLIES 3
Helper I

Hi,

i have tried

Total sales = CALCULATE(SUMX('table','table'[Project Value]),MIN('table'[Id],0))

Total sales = SUMX(values('table'[Id]),calculate(MAX('table'[Project Value])))

Both are working for me thanks for your support

Super User

Total sales : =
SUMX (
DISTINCT ( 'table'[Id] ),
CALCULATE ( DISTINCT ( 'Table'[Project Value] ) )
)

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.

Best regards, JiHwan Kim

Super User

@ranga_88 , Try like

Total sales = SUMX(values('table'[Id]),calculate(MAX('table'[Project Value])))

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements