Calculating the sum by dividing by a count of another column

[tmillerch]

Hello everyone,

Essentially what I am trying to do is take the hours of each consumerUniqueID and make sure that each consumer completes a monthly check-in of x hours of face to face service each month.  The last two columns are my attempts at trying to solve this and are both miserably wrong.

If anyone would be able to help me figure out a way to sum the hours of each consumerUniqueID by month that would be very much appreciated and/or how to determine which consumer was able to meet the requirement of 3 face to face hours for any given month.

Thank you.

[v-yalanwu-msft · Accepted solution]

Hi, @tmillerch ;

Try it.

Column = CALCULATE(SUM('Table'[Hour])/DISTINCTCOUNT('Table'[customerUnigueID]),FILTER('Table',[Month]=EARLIER('Table'[Month])))

or

Column 2 = CALCULATE(SUM('Table'[Hour]),FILTER('Table',[Month]=EARLIER('Table'[Month])&&[customerUnigueID]=EARLIER('Table'[customerUnigueID])))

The final show:

If not right, can you share the result what you want to output and you logic?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shaurya]

Hi @tmillerch,

I may have understood your problem partially only but this is what I think you want. The values in the Hour column should add up for all the rows with the same consumerUniqueID and Month.

Here's a DAX Formula to do that:

Sum of Hours = SUMX(FILTER('Table','Table'[consumerUniqueID]=EARLIER('Table'[consumerUniqueID])&&'Table'[Month]=EARLIER('Table'[Month])),'Table'[Hours])

Works for you? Mark this post as a solution if it does!