Add an average line for the company when I filter by employee

[janetcpa]

I'm calculating the average cost per unit that a SPECIFIC EMPLOYEE pays for a raw material over time and want to compare that to the company-wide average over time.  I put the employee name in as a filter.  However, if I use the analytics average (or even create a measure), this give me the average over time for that employee.  How can I plot both the average unit cost for an employee over time against the average unit cost for the company over time?

Here is a picture of what I have:

My purchases table has the date, the material purchased (e.g. Colbat), the employee who purchased the material, total purchase amount, total quantity, and average unit cost (this is a measure calculated by taking total purchases divided by total quantity).

I have a filter for the specific employee and the specific material (Tom Larson and Colbat in this made-up example). I want to show the average cost for that material for ALL employees over time and not just the employee I filtered for the chart.

At this point, all I can think of is building a matrix to get my company-wide averages over time for each of my 9 raw materials and then creating a new table with that information in it and plotting it as a combo graph.  Any other thoughts?

[AlexisOlson · Accepted solution]

You can define constant line(s) in the Analytics pane and use measures to set the value of where to place the line.

So you can define a measure that gives the company average and a measure that gives the employee average.

To get the company average use ALL in your measure to remove the employee filter context. E.g.

CompanyAvg = CALCULATE ( AVERAGE ( Table1[Cost] ), ALL ( Table1[Employee] ) )

[janetcpa]

Okay -- I played around with this a bit more including researching the "ALL" statement you mentioned.  This is what I wrote and it now works to give me the average from all employees regardless of what employee filter I put in the graph (YEAH!).

Company Avg Unit Cost =

DIVIDE(Calculate(SUM('Purchases'[Total Purchase]),ALL(Employees)), Calculate(SUM('Purchases'[Quantity]),ALL(Employees)))

[janetcpa]

I put that in as follows:

Company Avg Unit Cost = CALCULATE(Purchases[Avg Unit Cost],ALL(Employees[EmployeeID]))

Where [Avg Unit Cost] in is a measure in the purchases table. However, it's not calculating correctly.  I created a line chart with values of the avg unit cost measure (without the ALL stmt) and the new Company-wide measure as a secondary value.  I didn't filter by employee...so the two lines should match exactly.  However, the new measure isn't correctly calculting.

Here's my original average cost measure:

Avg Unit Cost = DIVIDE(SUM('Purchases'[Total Purchase]), SUM('Purchases'[Quantity]))

It sounds like I want this but for ALL employees...any way to put the ALL in to this statement?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file and show the expected result in a simple table format.