How to get Average column to sum correctly

[zrnooredeen]

I am working with one excel workbook that has one sheet. There are clients and personnel who work on multiple clients. In power BI, I have a table showing: the personnel, the total number of hours each work, the total number of each clients they worked on, and the average number of hours per client.

The total number of each clients was calculated by dragging the Client name on to the value section of the visualization, and selecting count of client name from the drop down. Afterwards I was able to calculate the average per client using this formula:

Average Per client =

DIVIDE(SUM('Sheet1'[Total Hours]), DISTINCTCOUNT('Sheet1'[Client name]))

The averages are correct for each personnel, but the sum of this column does not add up correctly. I've tried to watch multiple videos on youtube about this but could not understand the concept well. Can some one please explain the idea as to why it doesn't add correctly and how should I modify my formula.

Thanks!

[parry2k]

@zrnooredeen hope this will help you to understand why you are not getting correct result. I created a sample dataset to explain, in image below, left hand side shows how your measures are working, and right hand side shows how it suppose to be. Look at Total line under Distinct Client  measure

So right hand side, distinct count for client for each person is correct and that's why Avg works fine but at the total level, it give you distinct count of all the clients for all the persons and that is not sum of distinct client by each person, it is for whole dataset, and that;s why your avg at total line is not correct

[zrnooredeen]

@parry2k How would I fix the average column so that it totals correctly in that case? 

[parry2k]

@zrnooredeen sorry I missed to post the DAX measure expression. here it is

Distinct Client (Revised) = 
SUMX ( VALUES ( 'Table'[Person] ), 
CALCULATE ( DISTINCTCOUNT ( 'Table'[Client] ) )
)

[zrnooredeen · Accepted solution]

@parry2k I manage to get the correct average per client to calculate and the correct totals in each column. The thing is I don't understand how or why this works. In addition, can you please elaborate more on your first post as to why the average don't simply work?

This was the formula I ended up using: 

Average Hours Per Partner =

if(hasonevalue(Sheet1[Personnel]),DIVIDE(SUM('Sheet1'[Total Hours]), [Number of Partners Worked on]),sumx(values(Sheet1[Personnel]),CALCULATE([Hours]/[Number of Partners Worked on])))

[v-lili6-msft]

hi @zrnooredeen 

This looks like a measure totals problem. Very common. See this post about it
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Regards,

Lin