multiple facts with different granularity

[powerbiexpert22]

i need to show targetsales for each product in table visual however i am getting incorrect results. Target table is at month level granularity. I am not sure how i should model the relationship between sales and target fact tables. please see below file for your reference

https://drive.google.com/file/d/1A8_NXD9AP_pus9WD5JFP6QDgYnuvSGe9/view?usp=drive_link

[powerbiexpert22]

thanks a lot @Sergii24 

[powerbiexpert22]

Hi @Sergii24 ,

thanks, what is the purpose of creating below key column

[Sergii24]

As I mentioned: "P.S. you'll find calculated column "TargetKey" in target and sales table, feel free to delete it, it was a part of my testing" 😉

[Sergii24 · Accepted solution]

Hi @powerbiexpert22, I'd create a data model similar to this one:

1. Create a calendar table: it should filter target and sales.
2. Create a bridge table for product categories that will filter both target and products. Make sure to have bi-directional relationships from Category all down to the Product, it will assure the correct filtering when in case you filter a product

Here is a link to the updated pbix: https://we.tl/t-G6wG6MgtOE

Now, more general observation about your case: there are a lot of assumptions to do. Do you expect to filter sales by month only (granularity of Target) or daily (granularity of sales)? In the first case, aggregate sales by month as you don't need daily granularity. If the latter true - decide how you want to represent target when only 1 or few days selected (i.e. still show total month, split it per days, etc.). 

The same is valid for product: sales are at catergory level, but you have subcategories and products. What do you want to happen when you filter them?

So, make sure to design the expected behavior before. Only after that, move to model development: don't worry, with a right knowledge you can design whatever you want, but it's important that you know what you want 😉

Good luck!

P.S. you'll find calculated column "TargetKey" in target and sales table, feel free to delete it, it was a part of my testing 🙂