Weighted Average Measure on Single Table with Multiple Filters

[WishAskedSooner]

My apologies for being new to this forum. My first post of this problem was not specific enough. I have tried multiple times to reply with more information, but the forum never seems to let me. So, here is a second attempt with more information.

I have three tables:

Fact

DimE joined on EID

DimA joined on AID

and the following Data Model

I am trying to multiply Category A by Category B (not Category C) for each EID, then sum the individual results. I have created the following PIVOT to illlustrate:

So, the actual operation looks like the following:

(5*15 + 10*20) = 275

I am trying to do this in a measure using DAX versus actually pivoting my data to a new table. All of the potential solutions I have found point to joining two columns from two different tables to multiply. As you can see, my data sits in one table. Therefore, I need to find a way to filter the table by Category A and Category B then multiply the filtered values by EID versus some other jumbled order.

I hope this makes sense.. Thanks in advance for the help.

[DataInsights · Accepted solution]

@WishAskedSooner,

Try this measure:

Weighted Average = 
SUMX (
    VALUES ( DimE[EID] ),
    PRODUCTX (
        CALCULATETABLE ( 'Fact', DimA[Category] IN { "A", "B" } ),
        'Fact'[VAL]
    )
)