Latest Enterprise DNA Initiatives

Compare measures at different levels in a hierarchy

DAX Calculations
#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.

#4

Yes it’s definitely possible, you just need to create the correct virtual table for what you need.

For example instead of VALUES( Item)…

You might want to use SUMMARIZE( SalesTable, CustomerColumn, ItemColumn )

This will give you the unique combinations of these two columns together.

See how you go with this.

Sorting out what virtual table you want to iterate through is the key here, no doubt.

Think about everything single calculation and visualize what table do I need to iterate through to get that specific results I’m after. That’s how I think about it.

Chrs

#5

Sam,

While that solution did not work, you sparked an idea that did.

All I had to do was use the ALL function to make it include my item code. This gave me the proper calculation! Thanks for pointing me in the right direction!

Average Unit Margin = 
CALCULATE(
    [Margin per LB],
    ALL( Sales[Bulk Code] )
)
#6

Nice one. Looks good.

You see how you can adjust the context on this one either within an iterating function or with the filter area of the CALCULATE statement. Essentially the same thing.