Compare measures at different levels in a hierarcy


#1

What I’m trying to accomplish at a high level is to compare the average lead time on a production line to the average lead time the customer gives us. The idea is that if the customers expected lead time is greater than our lead time, then we can remove those items from our stocking program, or vice-versa.

I have a sales table that looks similar to this.

image

And an items table that looks similar to this.

image

I can create an average measure that will calculate my lead time and display it across the proper context, but what I want it to do is something like this. (this assumes we’re filtered to line 1).

image

The way I’d want this to work is to select a filter for the production line and the correct lead time to populate in the line lead time column.


#2

Ok trying to understand this fully here.

So you’re trying to work out averages?

To do anything like this what you want to be using is AVERAGEX

So for example of the Customer is filtered you want a formula that iterates through every leadtime that customer has giving you.

Something like this should do it.

AVERAGEX(
VALUES( Item ),
LeadtimeColumn )

Then to compare this the overall leadtime across all customers, you would want something like this.

AVERAGEX(
ALL( SalesTable ),
LeadtimeColumn )

This theoretically should get you want you need.

Then you can branch out from here to further calculations like VARIANCE.

Let me know how you go with this one.

For more info on AVERAGEX, see here


#3

Thanks for the response!

This is great stuff, but it doesn’t quite get me where I was hoping to go.

What I’m after is something like this (sorry if I wasn’t clear enough)

Customer Lead-time Line Lead-time Variance
Line 1 Customer 1 13 8.5 4.5
Line 1 Customer 2 4 8.5 -4.5

Is this possible in BI? The averagex formula defaults to the lowest level of the hierarchy. I want to force it to go to the level I want it to. I realize I could use calculate and filter it to that result, but it wouldn’t be dynamic, and dynamic is a must.

Thank you so much for considering this. I’m out of my depths at times with this program. But, I’m really thankful for this forum.