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 !

     

AilleryO's avatar
AilleryO
Memorable Member
4 years ago

Hi,

 

You can do it with DAX, you just need to consider the lines of your table as filters to reach your goal.

Here is an example, to group by invoice and calculate the average amount of invoices for each customer.

 

Average Amount of Invoices by client =
CALCULATE(//to modify the calculation context
AVERAGEX(//Average by iteration (line / line)
SUMMARIZE(Sales,//Name of table you want to group in
Sales[NumInvoice],//First column to be taken in the grouping
Sales[Date Invoice],//Second column to be taken in the grouping...
Sales[Date Due]),
[Sales Revenue Global]),//Calculation or Measure to calculate Average
Clients[Type Client]="XXX")//Filter on table clients if needed

 

Hope it helps