Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I'm looking to create a DAX measure that shows the best selling product.
I have the code here and it gives me results but the results are not showing me the best selling product.
Can ye assist and be able to show me where I've gone wrong?
The code is below.
Thank you in advance.
Solved! Go to Solution.
Hi @gbarr12345
This should fix it:
Best Selling Product =
FIRSTNONBLANK (
TOPN (
1,
VALUES ( Orders[Product Name] ),
CALCULATE ( SUM ( Orders[Sales] ) ) -- or replace with measure
),
1
)
The 3rd argument of TOPN needs to be wrapped in CALCULATE in order for the value of Orders[Product Name] to be converted to a filter. Alternatively, you could use a measure which is automatically wrapped in CALCULATE.
This article may be a good reference:
https://www.sqlbi.com/articles/understanding-context-transition-in-dax
Regards
Just confirming that Best Selling Product is returning the expected result? If so, great!
Regarding Best Selling Product Values, the DAX expression for the measure looks correct in itself. Are you able to test it in different contexts and compare against a visual showing sales by Product Name?
Hi Owen,
I can confirm all is ok now.
For the best selling product values it was an error on my end which is ok now.
Thanks a million for your help!
Hi @gbarr12345
This should fix it:
Best Selling Product =
FIRSTNONBLANK (
TOPN (
1,
VALUES ( Orders[Product Name] ),
CALCULATE ( SUM ( Orders[Sales] ) ) -- or replace with measure
),
1
)
The 3rd argument of TOPN needs to be wrapped in CALCULATE in order for the value of Orders[Product Name] to be converted to a filter. Alternatively, you could use a measure which is automatically wrapped in CALCULATE.
This article may be a good reference:
https://www.sqlbi.com/articles/understanding-context-transition-in-dax
Regards
Thank you for the response.
I've inputted that code and it doesn't seem to be showing me the top product. As you can see, the product should be Fellowes Super Stor/Drawer Files and the sales figure should be 12,599.55.
As per the screenshot from power BI the best selling product is correct but the best selling product value is showing as 16,692.85. The code that I use to get the best selling product value is below if you can also see where it's going wrong maybe?
Share the download link of the PBI file.
Hi Ashish,
It's all ok now. The issue is resolved. Thanks for your response!
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |