cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ranga_88
Helper I
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 ValuePlantStatusEffortsID
CTWPackaging180000Columbus On Hold0.001
CTWPackaging180000Columbus On Hold0.001
CTWPackaging54000DroghedaOn Track0.102
CTWPackaging54000DroghedaOn Track0.102
CTWPackaging50000CurtibaOn Track0.303
CTWPackaging50000CurtibaOn Track0.303
CTWPackaging26000JDFOn Track0.154
CTWPackaging26000JDFOn Track0.154
CTWPackaging55000ColumbusOn Track0.355
CTWPackaging55000ColumbusOn Track0.255
CTWPackaging55000ColumbusOn Track0.105
CTWProduct30000TuasOn Track0.606
CTWProduct30000TuasOn Track0.606

 

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
amitchandak
Super User
Super User

@ranga_88 , Try like

 

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

ranga_88
Helper I
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

View solution in original post

3 REPLIES 3
ranga_88
Helper I
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

View solution in original post

Jihwan_Kim
Super User
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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

amitchandak
Super User
Super User

@ranga_88 , Try like

 

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

View solution in original post

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors