Using ALL filter creates blank rows

[watje255_ju]

COGS TEST HB.pbix link to PBIX file

Hello,
I have two fact tables, one with all the sales and customer data, and one which I am using to pull the COGS.
There is no direct link between the two tables for specific orders and customers as the intergration between the sales platform where the sales data comes from and the GL where the COGS data comes from has not been created., but the stores and SKUs are the same.

I want to be able to calculate the GM for each sales order. I created an inactive relationship between the two fact tables and USERELATIONSHIP in the COGS measure. This has stopped COGS appearing for all SKUs/customers when not in the sales order. However, there are some instances when the invoice dates are different ,so there is no COGS data for that store and month and SKU.

If I remove the date filter using ALL, then values are returned for months with no orders, likewise if I use an average. HOw can I create a COGS pu measure that I can use when using columns from the Sales table? The ideal result would be the COGS PU US RELATIONSHIP MONTH table with out any blank COGS.

[rautaniket0077 · Accepted solution]

Hi @watje255_ju ,

Try replacing date from dimdate with date from factsales and format it as "Mmm yyyy"

[watje255_ju]

Awesome, thanks so much!

[watje255_ju]

Awesome, thanks so much!

[watje255_ju]

Hi, thanks for your reply, that is the problem that I am trying to fix, when there is no data in the underlying table for that SKU and Month, I want to pull the average COGS per unit for that SKU, so that there is a COGS value and we can still calculate a gross margin. This happens in the odd occasion because the GL date can be 1 day behind the subledger date or vice versa, thank you!

[some_bih]

Hi @watje255_ju part "to pull the average COGS per unit for that SKU" as there are no actuals for month December there is no average amount! In your table (marked with yellow above part" some details are from two tables, like fact and SKU. Why do you need table SKU ast there are just two values? I am not aware why, but the best would be to organize your model to have one fact table. Did you try to use SKU column in table, from Fact table? Also, having two fact tables, is not best practice as there is many to many relationships between fact and COGS table. Even, in this complex model (not according to best practice), the question is what average you want to get in case there is blank as current. I suggest to go to your model, granularity of data should be considered and create single fact table. I agree, the best would be to have data on transaction level. Still, if your workflow could not provide COGS data, you will need some alternative workaround to get some data (like average). Hope this help

[watje255_ju]

Thanks, unfortunately the COGS data and the sales data comes from different systems so there has to be two fact tables. IF there is no data for the month due to timing mistmatch, I want to pull the average COGS for that SKU as we still need to report a GM for that sale. Thanks for your time.

[some_bih]

@watje255_ju you are on good way to resolve this. take a time

[some_bih]

@watje255_ju you are on good way to resolve this. take a time

[some_bih]

Hi @watje255_ju in you measure COGS PU, underling table is COGS. In this table there are no data for December 2022 for SKU 20 ORDER HB, see picture below. SKU 21 for this details have data. Hope this help