Need help for SumProduct DAX

[blader1989]

Hi all,

I'm building a power BI report that will show the average product that was purchased by customer with total sales large than 0 because if customer returns the product, it will show negative number in report that's why we will only count the total sales large than 0. I also write a DAX that count distinct customer with total sales of product large than 0 as below, and the table show what we expected

Distinct count customer that total sales large than 0 = 
CALCULATE(DISTINCTCOUNT(raw[Customer Code]),
FILTER(raw,DISTINCTCOUNT(info[Product Name]) && [TotalSales] > 0
)
)

Now only the sumproduct like in my excel file with yellow hightlight outcome desire so I hope you can help me for this DAX. Thank you so much. Here is my files.

[blader1989]

HI @Anonymous ,

Thank you so much, you has helped me to solve my problem nearly 90% percent. Now only the sumproduct left because I wrote a sumproduct in power bi like below but the result is not correct as my excel file. You will see the reason why I need the row number DAX.

sumproduct = SUMX(raw, [Distinct count customer that total sales large than 0] * [Row Number])

My expect It should be like this so could you please help me to solve my DAX? Thank you in advance. I already update the latest file from my link drive above.

[blader1989]

Hi all, I think I found the way but still need your help that how can I add the row number next to the column following by my hightlight yellow into this visual table? 

[Anonymous · Accepted solution]

Hi @blader1989 ,

Try this:

Row Number = IF(HASONEVALUE('raw'[Product Name]),CALCULATE(DISTINCTCOUNT('raw'[Product Name]),'raw'[Product Name]<=MIN('raw'[Product Name])))

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly--How to provide sample data in the Power BI Forum--China Power BI User Group