cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DRossi
Helper II
Helper II

Find Distinct values in one column and return sum of values of another column

Hello,

 

I have a dataset that has columns Table1[FA_ID] and Table1[Job Duration].

 

I need to create a measure that goes like:

 

Find Distinct values in FA_ID and then through the corresponding FA_IDs, return the sum of Job Durations.

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

Not Tested but try

Measure =
Sumx(summarize(Table1,Table1[FA_ID], "_dur",max(Table1[Job Duration])),_dur)

OR

Measure = 
VAR __id = MAX ( 'Table1'[FA_ID] )
VAR __dur = CALCULATE ( MAX( 'Table'[Job Duration] ), ALLSELECTED ( 'Table1' ),  'Table1'[FA_ID] = __id ) 
RETURN CALCULATE ( sum ( __dur ), VALUES ( 'Table1'[id] ), 'Table1'[id] = __id )

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

4 REPLIES 4
amitchandak
Super User IV
Super User IV

Not Tested but try

Measure =
Sumx(summarize(Table1,Table1[FA_ID], "_dur",max(Table1[Job Duration])),_dur)

OR

Measure = 
VAR __id = MAX ( 'Table1'[FA_ID] )
VAR __dur = CALCULATE ( MAX( 'Table'[Job Duration] ), ALLSELECTED ( 'Table1' ),  'Table1'[FA_ID] = __id ) 
RETURN CALCULATE ( sum ( __dur ), VALUES ( 'Table1'[id] ), 'Table1'[id] = __id )

 

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

First measure and you nailed it.

 

I just added the square brackets around the last dur

 

Thanks very much.


amitchandak
Super User IV
Super User IV

When you put that in matrix and sum job Duration, You should get the same output? Or any other visual



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for your reply @amitchandak 

Yes this is true, however, becuase there are duplicates of FA ID, this means there are duplicates of job duration.

 

If I add them in to a matrix or chart, I can see that the count of FA IDs is the same as the count of job durations, if I change to distinct count of FA IDs then FA IDs reduce, great, but the job duration count is still the same as the count of FA IDs. Hence this would then skew the sum of job durations.

 

I can't change to distinct of job durations as there will be many the same. 

 

I need something that finds the DISTINCT FA ID and the corresponding job duration.

Hence why I would require a formula for the above.

Let me know what you think.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors