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.
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.
The context within the table is coming from the data table, but within the AVERAGEX your referencing the FTE table.
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.
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.
For more on measure branching, see below.