cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sydneyr896
New Member

Taking the Average of an Average Measure (Across a Category)

This is useful when you need to take the average of a measure (that is an average itself) across a category of data, for data points that occurred at different dates. (The date part is optional but can be useful when you are working with rolling data)

 

For example, let’s say you have 3 machines, and each machine has a different number of products that it makes, and each product has its own average error rate. The catch is that the machine began manufacturing the different products on different days.

 

And you want to know the average product error rate for each of the 3 machines, 2 quarters after it started manufacturing each of its individual products. Obviously it would be a lot easier if the machine started manufacturing all of its products at the same time, and each machine made the same amount of products, but life gets a little messy sometimes and we just have to roll with it. SO, here we go. Where to start?

 

This is assuming your starting data has a column with machine type, product type, and manufacturing start date, and you also have the error rates organized by products and quarters. You may need to create relationships depending on how your data is set up.

 

  1. Create date measures for the 2 quarters after manufacturing date.

Make a new column in your query first, using date.addquarters(column,1), and call it "1 Quarters After Mftg Start." Now, create a simple measure in your report:

 

1 Qtrs After Mftg Start = min('table'[1 Quarter After Mftg Start])

 

Do the same thing for 2 Quarters After.

 

  1. Since you probably want a rolling average instead of a snapshot, create a measure that averages the 2 quarters of error rates (this is assuming your error rates are captured in quarters. If not, you can use months, days, etc. for this problem).

 

Avg Error Rate 2 Qtrs After = (CALCULATE(SUM('Table'[Error Rate]),FILTER(ALL('Table'[Date]),'Table'[Date]=[1 Quarter After Mftg Star]))+CALCULATE(SUM('Table'[Error Rate]),FILTER(ALL('Table'[Date]),'Table'[Date]=[2 Quarters After Mftg Start])))/2

 

This gives you the 2 quarter rolling average error rate for each product, 2 quarters after its manufacturing start date.

 

  1. Take the average of these product error rate averages, to give you an average error rate 2 quarters after product start manufacturing date for each machine.

 

To do this, you can create a quick measure, average per category. Where the field is the measure you just created [Avg Error Rate 2 Qtrs After] , and the category is "Product."

 

This might seem counterintuitive because you are wanting to group by machine, but if you categorize by machine, the measure is only going to divide by 1 since there is only 1 machine per category. Using "Product" as the category tells the measure to divide by the number of products in the category which will give you an accurate average per machine. Here is what the DAX looks like.

 

Avg Error Rate 2 Quarters After average per Product =

AVERAGEX(

    KEEPFILTERS(VALUES('Table'[Product])),

    CALCULATE([Avg Error Rate 2 Quarters After])])

)

 

 

Now, when you put all this onto a table, make sure to include a "subtotal" line if you want to see the average per machine.

 

When you put this measure onto a bar graph with the machines as your axis, it should show you the average error rate per machine 2 quarters after manufacturing start dates.

 

 

I hope this was helpful. I have been trying to solve this puzzle for a while now and thought I would share how I cracked it. Let me know if you have any questions!

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.