Forum Discussion
Solved
Using ALL filter creates blank rows
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 tabl...
Hi watje255_ju ,
Try replacing date from dimdate with date from factsales and format it as "Mmm yyyy"
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
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.
watje255_ju you are on good way to resolve this. take a time
watje255_ju you are on good way to resolve this. take a time