Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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% |
Solved! Go to Solution.
Hi,
you can use this measure
(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,
you can use this measure
(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 !
Brilliant solution, Serpiva! Thank you!
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
Sorry
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |