All Results for one table to appear when another table is filtered

[marksaba]

I have two data sets with a Unique ID that is Product ID - Customer ID - Date. One data set is a SQL import. The other data set is a UNION table of several forecast files. The goal is to be able to compare Actual Sales (SQL Import) to Forecast and be able to slice by forecast. 

I have created the necessary relationships, a Many to Many Cardinality with Both as the Cross Filter direction. Below shows the correct value for Forecast Revenue (sum of the two forecast files) and Invoice Sales (SQL Import).

However, when I filter to one of the forecast files, then Invoice Sales reduces as well, as you can see below. I believe this is because, when filtering the Forecast Date, I only then see Invoice Sales values of the Product ID - Customer ID - Date that also appear in the Forecast Revenue file from 1/3/23. I want to always see the Invoice Sales total, regardless of the Forecast Date. Meaning, the difference of $383.7M in the above screenshot to the $296.3M in the below screenshot ($87.4M) are Invoice Sales that were not forecasted for, at least at the same Product ID-Customer ID-Date. My goal is to be able to show all of the Invoice Sales and Forecast. Meaning, when I filter to 1/3/23 for Forecast, I want to see everything that either had Forecast on 1/3/23 and everything that had Invoice Sales. So if a Product ID-Customer ID-Date did not have a Forecast but did have Invoice Sales, I still want that to be factored in but just show $0 as the Forecast.

 

Can anyone help?

Thanks

[audreygerred]

Hello, rather than joining your two fact tables directly together, I would suggest a Star-Schema model. Your fact tables would be the Sales and the Forecast and you would get the facts to work together by utilizing dimension tables (i.e. Product, Customer, Date). By switching to a Star-Schema your forecast table won't filter the Sales table. You can utilize the Forecast Date from your Forecast fact table to choose which forecast you want to show, or you can add in a second date table so that you can filter both the sales and forecast independantly.

http://powerbiwithme.com/2023/07/30/the-star-schema-edition/

[marksaba · Accepted solution]

Resolved by adjusting the Cardinality of the relationship!

[calerof]

@marksaba,

Do you have a Date table in your model?

F