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
I think your data modeling is very good 👌 I like the way you put the dimension tables at the top and the fact tables at the bottom, and used easy way to identify all the measures in one location which I think is a good idea. One of the ways to achive your required output is to use ALL function to calculate the % of sales by category and multiply the costs to be allocated by that. The steps I have taken are as follows.
First identify the sales excluding the "Other" category.
Then use the all function to remove filter for the category using the output above.
Then, divide the 1st measure by the 2nd one to get the % of the sales excluding the "Other" category by customers and by invoice numbers.
Next identify the COGS to be allocated:
Then multiply the COGS to be allocated by the % of sales category by customers and invoice numbers.
Then, combine the allocated and not allocated COGS to get the total COGS in one line.
Then finally show the GM by category after COGS allocation.
The resultant output is as shown below.
The above is just one of multiple ways to achieve your required output, and I suppose you can also try to combine all the steps above in just one big formula using VAR and RETURN (haven't tried, but maybe). But I find it easier to break the steps down into bite-sized smaller components. The advantage of smaller formulas is that is it is easier to debug while simultaneously visualizing output of the each step taken by showing those measure outputs in a matrix table and checking that they are in line with your expected results.
I attach an example pbix file.
Best regards,
Thank you very much DataNinja777 for your kind words about my data modeling and your solution, which looks absolutely right.
After I have worked a bit with a few visualizations, it has turned out that I get an incorrect calculation on rows if I insert a column from the fact table InvoiceRow on the rows. In that situation, it looks like it's because measure Row.Sales(<>Other) (All) is calculating an incorrect value.
I cannot figure out why this error appears, and I therefore really hope that you have the opportunity to help me solve that challenge as well, so that the following visualization can be made:
This table is fundamental in my Power BI model.
I will appreciate to hear from you again.
- DataNinja777
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,
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.
