Average - Group by calculation

[Anonymous]

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%

[serpiva64 · Accepted solution]

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 !

[AliceW]

Brilliant solution, Serpiva! Thank you!

[Anonymous]

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]

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) 

[AilleryO]

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