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

[DRossi]

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.

[amitchandak · Accepted solution]

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

[DRossi]

First measure and you nailed it.

I just added the square brackets around the last dur

Thanks very much.

[amitchandak]

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

[DRossi]

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.