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

[Anonymous]

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!

[technolog · Accepted solution]

First, you need to create date measures for the 2 quarters after the manufacturing date. In your query, add a new column using date.addquarters(column,1) and name it "1 Quarters After Mftg Start". Then, in your report, create a measure:

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

Repeat the same for "2 Quarters After Mftg Start".

Next, you'll want to calculate the average error rate for each product, 2 quarters after its manufacturing start date. Since you're looking for a rolling average, create a measure:

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 measure gives you the 2 quarter rolling average error rate for each product.

Now, to get the average error rate for each machine, 2 quarters after the products started manufacturing, you'll need to take the average of the product error rate averages. 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". Even though you want to group by machine, use "Product" as the category. This is because if you categorize by machine, the measure will divide by 1 since there's only 1 machine per category. By using "Product", the measure divides by the number of products, giving you an accurate average per machine. The DAX for this is:

Avg Error Rate 2 Quarters After average per Product = AVERAGEX(KEEPFILTERS(VALUES('Table'[Product])), CALCULATE([Avg Error Rate 2 Quarters After]))

Finally, when you display this on a table, include a "subtotal" line to see the average per machine. If you plot this measure on a bar graph with machines as your axis, it will show the average error rate per machine, 2 quarters after the manufacturing start dates.

I hope this clears things up! It's a bit of a complex problem, but once you break it down step by step, it becomes more manageable. Let me know if you need any further clarification!