Forum Discussion
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 the workers time / the average working time in the department.
I am not able to calculate that average working time in the department part.
So what I would like to reach is the "Working time of the Worker / Average working time by department" in the table below.
Is it possible in DAX or should I use SQL?
| WorkplaceTable.Workplace | WorkplaceTable.Department | WorkerTable.Worker | Working time of the Worker | Working time by department | Average working time by department | Working time of the Worker / Average working time by department |
| Company A | Department A.A | Worker A.A.A | 23 | 589 | 196 | 11.7% |
| Company A | Department A.A | Worker A.A.B | 345 | 589 | 196 | 175.7% |
| Company A | Department A.A | Worker A.A.C | 221 | 589 | 196 | 112.6% |
| Company A | Department A.B | Worker A.B.A | 554 | 8,442 | 2,814 | 19.7% |
| Company A | Department A.B | Worker A.B.B | 234 | 8,442 | 2,814 | 8.3% |
| Company A | Department A.B | Worker A.B.C | 7,654 | 8,442 | 2,814 | 272.0% |
| Company A | Department A.C | Worker A.C.A | 432 | 576 | 192 | 225.0% |
| Company A | Department A.C | Worker A.C.B | 21 | 576 | 192 | 10.9% |
| Company A | Department A.C | Worker A.C.C | 123 | 576 | 192 | 64.1% |
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))returnPerc(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 !
5 Replies
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))returnPerc(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 !
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!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)
Brilliant solution, Serpiva! Thank you!
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 neededHope it helps
