Forum Discussion

WishAskedSooner's avatar
WishAskedSooner
Continued Contributor
2 years ago
Solved

Weighted Average Measure on Single Table with Multiple Filters

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.

  • WishAskedSooner,

     

    Try this measure:

     

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

     

     

1 Reply

  • WishAskedSooner,

     

    Try this measure:

     

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