cancel
Showing results for
Did you mean:
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
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

Proud to be a Super User!

4 REPLIES 4
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

Proud to be a Super User!

Helper II

First measure and you nailed it.

I just added the square brackets around the last dur

Thanks very much.

Super User IV

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

Proud to be a Super User!

Helper II

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.

Announcements

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