Cost allocation with two fact tables

[Lars_Denmark]

I have run into a problem with distribution that I cannot solve myself, and I therefore hope that there is someone here who can help.

I have two sales tables, consisting of an Invoice Header table and one with associated Invoice Lines. On each invoice header, there is information about total sales and gross profit. The table with invoice lines contains information about the item, quantity sold, and unit price. For each invoice header, there are one or more lines with item numbers belonging to the "Other" category, as well as invoice lines with item numbers belonging to all other categories.

I now want to be able to calculate the Gross Margin for each invoice line and for this, a product cost must be calculated for each invoice line.

There is no profit on the sale of items belonging to the category "Other", and therefore the cost of goods sold (COGS) on these lines must be equal to the total sales of the line (quantity sold * unit price). The remaining part of the invoice's COGS must then be distributed to the remaining invoice lines based on the line's sales as a percentage of the total sales for all lines excluding the lines with category "Other".

When the COGS has been distributed from header to the lines, the Gross Margin must be calculated. It must then be possible to make a statistic for each category with information on sales of goods, Gross Margin and Gross Margin%.

The distribution should be as shown in the example below:

My PBIX file can be downloaded from WeTransfer with this link: https://we.tl/t-Ano1FDOKxq 

Any help is greatly appreciated!

[DataNinja777]

Hi @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,

[Lars_Denmark]

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 · Accepted solution]

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,

[Lars_Denmark]

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,

[Lars_Denmark]

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.

[3CloudThomas]

You would need to combine these into one table and distriubte the total sales and profit proportionality by number of lines or something on those lines.
That is a high level thought on this, but to implement in a chat like this would not be possible.