Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all. Looking for some data modelling expertise and I have not been able to figure this out for a couple days.
I have a large data extract outlining the services and budget associated for each organinzation. The table basically looks something like this:
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:
Correct totals, but slicer filtering not exactly correct due to modelling logic:
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.
Solved! Go to Solution.
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
You should change the bi-directional filtering to single. Double click on the rogue relationships and edit the relationship accordingly (from DIM to FACT)
Proud to be a Super User!
Paul on Linkedin.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |