DAX - Sum values in many-to-many relationships

Question

I'm working in Excel, PowerPivot.

I have 2 tables. Table 1 has locations of inventory types. Notice that there can be several items of a type in a room:

Kategory	Room	Serial number
Pump	Room 1	SN1
Pump	Room 1	SN2
Pump	Room 2	SN3
Xray	Room 2	SN5
Xray	Room 3	SN6
Pump	Room 3	SN4

Table 2 has which payers are responsible for each room. Note that several payers can SHARE paying for a room.

Payer	Room	Percent
P1	Room 1	70%
P2	Room 1	30%
P3	Room 2	100%
P2	Room 3	100%

I want to sum up "How many pumps should each payer pay for, based on shares of each room?"

The result for P1 should be "2 pumps in a room where P1 pays 70% = 1,4".

The result for P2 should be "2 pumps in room 1 x 30%, plus 1 pump in room 3 = 1,6"

I cannot make a direct relationship between the tables based on Room columns, as there are no unique values.

I've tried putting a table with unique room numbers in between Table 1 and Table 2, and creating a measure in Table 1

Share:=CALCULATE(SUM(Table2[Percent]);Table1)

but that doesn't give the expected result. Instead I get P1 = 0,7 (1 room x 70%) and P2 = 1,3 (1 room x 30% + 1 room 100%).

Is there a way I could (by DAX) create a table that contains joins of Table1 and Table 2, where Room(Table1)=Room(Table2)? Or something to the same effect?

The explanation is simplified. Of course the model contains lots of other columns that I want to use in data analysis.

score 1 · Answer 1 · answered May 05 '23 at 18:38

Perhaps it's possible to achieve this using a bridge table, though I don't see any great need. Try the following measure:

=
VAR T1 =
    SUMMARIZE (
        Table2,
        Table2[Room],
        Table2[Percent],
        "Number of Pumps",
            COUNTROWS (
                FILTER (
                    Table1,
                    Table1[Room] = MIN ( Table2[Room] )
                        && Table1[Kategory] = "Pump"
                )
            )
    )
RETURN
    SUMX ( T1, [Number of Pumps] * Table2[Percent] )

which can be incorporated into a Pivot Table along with the Payer field from Table2.

DAX - Sum values in many-to-many relationships

1 Answers1