Forum Discussion

crln-blue's avatar
crln-blue
Post Patron
2 years ago
Solved

Imitate Excel SumProduct

Hello everyone!   I've been looking for posts related to Excel's sumproduct and most of the results yielded to using SUMX and in its argument, multiply the tables.   I've tried it but the reults ...
Need Help
  • crln-blue's avatar
    crln-blue
    2 years ago

    Hello, I think I got this right but somehow I'm getting it also wrong?? Haha

     

DimaMD's avatar
DimaMD
Solution Sage
2 years ago

Hi crln-blue  The first step is to count the correct number of IDs

# of unique IDs = 
CALCULATE( 
    COUNT('Table'[IDs]), 'Table'[Values] > 0
    )

Second step in VAR _result add ALLEXCEPT also for correct summary you can use ISINSCOPE looks at given measure

Weighted Average = 
VAR _weight_avg = 
    SUMX( 'Table', 'Table'[Values] * [# of unique IDs])
        
VAR _result = DIVIDE(_weight_avg, CALCULATE([# of unique IDs],ALLEXCEPT('Table','Table'[Type])))
RETURN 
IF( ISINSCOPE('Table'[Type]),[Test Wt Avg],_result)

is this your desired outcome?