Forum Discussion

zrnooredeen's avatar
zrnooredeen
Regular Visitor
6 years ago
Solved

How to get Average column to sum correctly

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!
Need Help
  • zrnooredeen's avatar
    zrnooredeen
    6 years ago

    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])))

5 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      zrnooredeen
      Regular Visitor
      6 years ago

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

      • parry2k's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

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

         

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