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,
Super UserHi 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,
Unfortunately, it has turned out that cost allocation is not calculated correctly when there is a calculation of Gross Margin and GM% for each Category, where InvoiceNo is not included in Rows.
It seems that the problem is caused by the fact that Row.Sales(<> Other) (ALL) is calculated for all rows shown in the table, and this results in Row.GM% being the same value for all categories. The reason for this is probably that there is no filter on InvoiceNo in the table.
If I insert the InvoiceNo on the rows everything is perfect.
I have tried to solve the problem by inserting calculated columns in the table InvoiceRow, to have the allocated cost value on each row, but it has not been possible for me to do this.
I therefore hope that you or another skilled Power BI expert can help solve this challenge - then I have a perfect model.
- DataNinja777
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.