cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mcinnisbr
Advocate I
Advocate I

Data modelling multiple fact tables - Many to Many relationship

Data modelling - Multiple fact tables / Many to Many relationship
2m ago

Hi all.  Looking for some data modelling expertise and I have not been able to figure this out for a couple days.  

PBIX file 

 

I have a large data extract outlining the services and budget associated for each organinzation.  The table basically looks something like this:

  • location,
  • a parent service type,
  • a list of child service type asscoiated with the parent service type
  • a budget 
    mcinnisbr_0-1652714971652.png

     

     

The issue, each budget item only applies to the Service Parent Category. The granularity of the budget does not break down to the Service Child type, yet the Service Child is included.  Therefore, the budget items are duplcted if the Service Parent Category includes more than one Service Child Type.  For instance, Org A essentially has a budget of 10$, yet any calculation is wrong because Org A has 3 Service Children and therefore duplicates the budget sum to 30$. 

Similar to Org B.  It has 2 Service Parent Categories.  The true budgets should be 35$ for Org B, not 65$.   Org C sum is correct at 20$ simply because it only has one Service Child associated with Service Parent Category

 

I could simply remove the Service Child type column and work from the Service Parent Category and get accurate results.  However, our organization also wants to visualize the service counts for each organization, along side the total budget associated with each Service Parent Category.  So, I'm confused how to model out this many to many type of relationship in order to get proper counts.  I've tried junction tables, bridge tables, dividing up the main table into to fact tables (Service Parent category tables with budget, Child service table)  lookup table/distinct tables, but can't quite find the correct configuration, to get this to filter properly.  

 

My ideal situation is a report (with slicers for each column) that properly filters this data with the correct budget totals, yet, retain the proper count of services associated with each organiztion, or vice versa, Organizations associated with each child service.  And i guess one issue i'm having is Service Parent Categories slicer properly filtering the Service Children totals asscoiated with it. Kind of can't make that connection in my model properly as you can see below.  Essentially, a modelling issue for 2 fact type columns.  

 

Example report, with incorrect budget totals, based on unsplit table:

mcinnisbr_1-1652714971817.png

 

 

Correct totals, but slicer filtering not exactly correct due to modelling logic:

mcinnisbr_2-1652714971807.png

 

 

I haven't the clearest plan to either break this model out, or keep it much simpler than this.  Any help would be great!  Thanks.  

mcinnisbr_3-1652714971859.png

 

 

1 ACCEPTED SOLUTION
mcinnisbr
Advocate I
Advocate I

Well, I may have solved this for myself.  Using the full table, and not splitting out in DIM tables, this measure seems to work quite well in NOT duplicating the budget sum values.  SUM values come through perfectly across all slicers combinations.  

 

 

X =
SUMX (
    SUMMARIZE ( FullTable, FullTable[Service Parent Category], FullTable[Budget] ),
    FullTable[Budget]
)

 

Basic explanation:  relies on a 1:1 relationship between Service Parent Category & Budget. 

In this example, it says "make a table from FullTable with only the unique combinations (distinct values) of Service Parent Category & Budget. The SUMX says "sum the Budget column from the SUMMARIZE table. SUMX is necessary because I want to specify what table the column Budget is in: my SUMMARIZE table, not the Query1 table.

 

Source for my insight:
powerbi - Power Bi: Calculate sum of column value with distinct other column values - Stack Overflow

View solution in original post

2 REPLIES 2
PaulDBrown
Super User
Super User

You should change the bi-directional filtering to single. Double click on the rogue relationships and edit the relationship accordingly (from DIM to FACT)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






mcinnisbr
Advocate I
Advocate I

Well, I may have solved this for myself.  Using the full table, and not splitting out in DIM tables, this measure seems to work quite well in NOT duplicating the budget sum values.  SUM values come through perfectly across all slicers combinations.  

 

 

X =
SUMX (
    SUMMARIZE ( FullTable, FullTable[Service Parent Category], FullTable[Budget] ),
    FullTable[Budget]
)

 

Basic explanation:  relies on a 1:1 relationship between Service Parent Category & Budget. 

In this example, it says "make a table from FullTable with only the unique combinations (distinct values) of Service Parent Category & Budget. The SUMX says "sum the Budget column from the SUMMARIZE table. SUMX is necessary because I want to specify what table the column Budget is in: my SUMMARIZE table, not the Query1 table.

 

Source for my insight:
powerbi - Power Bi: Calculate sum of column value with distinct other column values - Stack Overflow

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors