Forum Discussion
Data modelling multiple fact tables - Many to Many relationship
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
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