Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
powerbiexpert22
Post Prodigy
Post Prodigy

multiple facts with different granularity

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_0-1737105464964.png

 

 

powerbiexpert22_1-1737105505103.png

 

1 ACCEPTED SOLUTION
Sergii24
Super User
Super User

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

Sergii24_0-1737108920415.png

 

  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 🙂

View solution in original post

4 REPLIES 4
powerbiexpert22
Post Prodigy
Post Prodigy

thanks a lot @Sergii24 

powerbiexpert22
Post Prodigy
Post Prodigy

Hi @Sergii24 ,

thanks, what is the purpose of creating below key column

 

powerbiexpert22_0-1737115480080.png

 

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
Super User
Super User

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

Sergii24_0-1737108920415.png

 

  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 🙂

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.