how to translate Sum product formula in power bi-CodePudding

I have this data table, where i want to calculate the final score based on CI value my excel formula is =SUMPRODUCT(--(H4:H20>0),H4:H20,E4:E20)/SUMIF(H4:H20,">0",E4:E20)

CodePudding user response：

The formula you are looking for is

final score = 
DIVIDE(
    SUMX(
        'Table',
        'Table'[CI] * 'Table'[score]
    ),
    CALCULATE(
        SUM('Table'[CI]),
        'Table'[score] > 0
    )
)

CodePudding user response：

You can test this:

Your_Measure =
ROUND (
    DIVIDE (
        SUMX ( SumProduct, IF ( SumProduct[score] > 0, [score] * [CI] ) ),
        SUMX ( SumProduct, IF ( [score] > 0, [CI] ) )
    ),
    6
)

Result It produces :

CodePudding user response：

You can follow the link to find help.

The solution in the link is for PowerQuery

https://www.myonlinetraininghub.com/excel-forum/power-query/countifs-or-sumproduct-in-power-query-or-dax