Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
janetcpa
Frequent Visitor

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

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:

 

Cost per unit.png

 

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?

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1637436659510.png

 

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] ) )

 

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

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

 

AlexisOlson_0-1637436659510.png

 

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] ) )

 

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)))

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?

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.