DAX formula involving Variable and AverageX

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:

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.

Thanks

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.

Go through this course module when you can.

This is what I have now


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.

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

…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.

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

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