Forum Discussion
Need help for SumProduct DAX
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.
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 TeamIf 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
3 Replies
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?
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 TeamIf 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
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.
