Forum Discussion
Cost allocation with two fact tables
Hi Lars_Denmark ,
In order for your allocation % to also respect the LineNo filter for your % of sales calculation you can tweak the two dax measures as follows:
First, modify your denominator of the % of sales measure by including all function to ignore filter for the LineNo field.
The above measure is then used in the % calculation in your data model.
Also, another part you need to modify is the following measure by including the yellow highlighted part for the same reason as above.
The resultant output produces the consistent output for sales % allocation respecting also the LineNo field.
I attach the updated pbix file.
Best regards,
Hi Lars_Denmark ,
To me, with and without the InvoiceNo seem to be yielding the same Row.GM% as it is. Please could you point to me which part you are not seeing the expected result?
Thank you & best regards,
Thank you for taking the time to respond so quickly.
In the two tables (2 and 4 from top) where you only have Category as a row, GM by Category and Row.GM% are correct in the Total. But when you look at GM by Category and Row.GM% for each category, they are not correct.
I have created the table below in Excel, where at the bottom highlighted in yellow you can see the expected value for GM by Category and Row.GM% for each category.
It is based on the calculations per category, which is created at invoice level.