Average of Column Parent Total

[Anonymous]

I am struggling with a DAX formula to calculate the average of the column parent. I would appreciate your help a lot on this.

I am presenting a simiplified example version below of, let's say, a retail store active in a few cities offering products from two departments: Bicycles and paintings.

I am trying to calculate an average Sales figure on employee level per City and Per department to be able to benchmark employees to other employees from their respective store and department.

Please see the screenshot. The column highlighted in yellow, which I have partly filled manually, is what I am trying to achieve in DAX. 

With this I can calculate a deviation of the employee versus his peers.

Thank you so much in advance.

[Anonymous · Accepted solution]

Figured it out myself with this easy formula. Knew it was going to be easy but this...

Average Sales per Employee within department and city =

CALCULATE (

AVERAGE(Sales[Sales]);filter(

ALL(Sales[EmployeeNr]);Sum(Sales[Sales]) <> 0))

[jthomson]

I'm not sure why you would necessarily need a DAX formula other than a standard average - if you were to put everything into a matrix with your measure being the average of your sales column, then putting city into the row section and department into the column section ought to show that average as a subtotal in the relevant places?

[Anonymous]

I would like to create a difference measure to peers and create nice visuals and trend lines rather than have it in a matrix. 

I believe I need DAX for this, but correct me if I am wrong 😉