Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Average - Group by calculation

Hi,  I am trying to calculate average and I am trying to calculate measure which will return the following: We have some workers and I would like to calculate a measure in DAX which will return...
Need Help
  • serpiva64's avatar
    serpiva64
    4 years ago

    Hi, 

    you can use this measure

    %WTofWorker/AvWTbyDept = var WTbyDept = CALCULATE([WTofWorker],ALL('Work'[WorkerTable.Worker]))
    var NrofWorkerbyDept = calculate(count('Work'[WorkerTable.Worker]),all('Work'[WorkerTable.Worker]))
    var AvWTbyDept = DIVIDE(WTbyDept, NrofWorkerbyDept)
    var Perc = if (HASONEVALUE('Work'[WorkerTable.Worker]), DIVIDE([WTofWorker],AvWTbyDept))
    return
    Perc
     

    (you can divide the formula in many steps)

     

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !

     

serpiva64's avatar
serpiva64
Solution Sage
4 years ago

Hi, 

you can use this measure

%WTofWorker/AvWTbyDept = var WTbyDept = CALCULATE([WTofWorker],ALL('Work'[WorkerTable.Worker]))
var NrofWorkerbyDept = calculate(count('Work'[WorkerTable.Worker]),all('Work'[WorkerTable.Worker]))
var AvWTbyDept = DIVIDE(WTbyDept, NrofWorkerbyDept)
var Perc = if (HASONEVALUE('Work'[WorkerTable.Worker]), DIVIDE([WTofWorker],AvWTbyDept))
return
Perc
 

(you can divide the formula in many steps)

 

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi serpiva64, 

    One more question - your solution is brilliant - how should I take care of when we have workers where there are no value regarding the working time and how did you define the 

    "WTofWorker" variable? 

    Thanks!
    • serpiva64's avatar
      serpiva64
      Solution Sage
      4 years ago

      Sorry

      WTofWorker = sum('Work'[Working time of the Worker])
      regarding the first question i think you have to count number of worker of each department not from the fact table but from the dimension table (if you have otherwise you have to create it) 
       
  • AliceW's avatar
    AliceW
    Power Participant
    3 years ago

    Brilliant solution, Serpiva! Thank you!