Need DAX to replace excel function

[kxj54590]

Hello folks,

I have this below excel function where i need to have dax to it in power bi, how can it be written?

=SUMPRODUCT(G2:G16,$F2:$F16)/SUMIF(G2:G16,">0",$F2:$F16)

G = Tier

F= Product weight

Thanks

shekar

[V-lianl-msft]

Hi @kxj54590 ,

The SUMX function will allow you to sum things across tables.It may like SUMPRODUCT in Excel.

You could try to create a measure like this:

Measure = 
var sum_p = CALCULATE(SUMX(Sheet1,Sheet1[Units Sold]*Sheet1[Sale Price]))
var sum_if = CALCULATE(SUM(Sheet1[Sale Price]),FILTER(ALL(Sheet1),Sheet1[Units Sold]<1000))
return DIVIDE(sum_p,sum_if,0)

You can also refer to these thread:

https://community.powerbi.com/t5/Desktop/Sumproduct-formula-in-PowerBI/m-p/696519 

https://community.powerbi.com/t5/Power-Query/Sumproduct-with-Power-Query/td-p/1012427 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@kxj54590 it will be easier if you throw sample data with the expected output.