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

 

 

zrnooredeen's avatar
zrnooredeen
Regular Visitor
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])))