DAX formula involving Variable and AverageX

DAX DAX Calculations
1

I’m trying to create a DAX formula to calculate production volume per direct labour according to selected period of time, but can’t get the correct outcome.

My pbix file contains 2 datasets - production volume is captured in one of the datasets, while direct labour headcount is captured in another dataset.

Calculated result is incorrect for below measure intended to calculate production volume produced per direct labour headcount:

Prodvol per DL = 
VAR 
    prodvol = CALCULATE(sum(data[Production Volume]))
VAR 
    AvgDL = AVERAGEX(filter(FTE,AND(FTE[Line]="Total direct labour",FTE[FTECat]="FTE Total")),'FTE'[Value]) 
RETURN
    Divide(prodvol,avgDL,0)

Pls advise how to fix the formula; details available in the sample pbix file in below link:

2

Can you please expand on your questions a little within the forum post.

This is to help with understanding more about the scenario you are dealing with.

Please review here for how to ask an optimized question.

https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30

Thanks

3

First the model just needs to be re-organized a little bit, so it’s easy to understand what’s happening.

It needs to be clear what is a lookup table and what is a fact table. Currently it is not so.

image
image755×563 12.4 KB

Go through this course module when you can.

This is what I have now

image
image785×463 10.6 KB

It would honestly help if you were able to show also what the intended results it.

You have to remember I have no idea about your data. I can re-arrange a formula but still I might not have the intended answer.

This information would just assist in getting the right answer quicker. That’s all I’m suggesting.

Ok just be conscious or writing variable like this.

image
image829×213 9.87 KB

It’s just better to create a simple measure like total volumn and branch out into this measure from that initial core one.

image

Also I would break out these into individual measures, this way you can actually see what’s happening on both side of the DIVIDE.

Quickly by doing this I can see a problem

image
image1121×690 30 KB

…this result is not filtering correctly as per the context of the table.

image

The context within the table is coming from the data table, but within the AVERAGEX your referencing the FTE table.

image

And there’s no relationship between these two tables in your model.

image
image759×233 4.86 KB

This is right by the way, there shouldn’t be any relationship between these two.

But you need to make sure you are filtering or creating context in your table from your lookup table.

For example

image
image1122×280 26.5 KB

Hopefully I’ve given you a number of ideas here to work through in the future when you come across a problem like this.

You end formula can be simple like this when you use measure branching techniques.

image

For more on measure branching, see below.

Thanks
Sam