March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hi all,
I have a list of products and sales revenues. I want to list only 10 products which have the highest sales value. What kind of function can I use in this case?
Saying the product column is "Product" and the sales revenue is "Sales".
Thank you.
Solved! Go to Solution.
when you say its wrong , is it because the total is wrong? are you filtering on anything else? or is because its not the top, you should change your asc to desc if you want the top
in terms of the measure you should be able to, for readbility though i would probably change the measure to this
Top 2 =
VAR totalSales =
SUM ( financials[ Sales] )
RETURN
CALCULATE (
totalSales,
FILTER (
VALUES ( financials[Product] ),
IF (
RANKX ( ALL ( financials[Product] ), [Total Sales],, desc) <= 2,
[Total Sales],
BLANK ()
)
)
)
Proud to be a Super User!
@s15 first you need to rank your data by you sales
then you should return only where top 10
this video is helpful ti figure it out
https://www.youtube.com/watch?v=hX-s1htBdmk
Proud to be a Super User!
I know I can rank by using filter but I'd like to learn more from DAX.
Thank you @vanessafvg
I followed the measure in the video but the ranking is not correct.
Firstly I created a measure to reflect to the Sales column.
Total Sales = sum(financials[ Sales])
Secondly I created the measure below
Top 2 = CALCULATE([Total Sales], FILTER(VALUES(financials[Product]), IF(RANKX(ALL(financials[Product]),[Total Sales],,ASC)<=2,[Total Sales],BLANK())))
Another question is: Do I have to create the first measure? Can I replace [Total Sales] by SUM((financials[ Sales]) in the second measure directly?
Thank you
when you say its wrong , is it because the total is wrong? are you filtering on anything else? or is because its not the top, you should change your asc to desc if you want the top
in terms of the measure you should be able to, for readbility though i would probably change the measure to this
Top 2 =
VAR totalSales =
SUM ( financials[ Sales] )
RETURN
CALCULATE (
totalSales,
FILTER (
VALUES ( financials[Product] ),
IF (
RANKX ( ALL ( financials[Product] ), [Total Sales],, desc) <= 2,
[Total Sales],
BLANK ()
)
)
)
Proud to be a Super User!
Thanks for this expression! Helped out a lot!
I am sorry, but this solution is wrong. When I trie this, it shows the complete total, not the topn
this does neot work:
Top 2 =
VAR TotalSalesVar =
SUM ( tblfinancials[Sales] )
RETURN
CALCULATE (
TotalSalesVar;
FILTER (
VALUES ( tblfinancials[Product] );
IF (
RANKX ( ALL ( tblfinancials[Product] ); TotalSalesVar;; desc) <= 2;
TotalSalesVar;
BLANK ()
)
)
)
@vanessafvg Thank you for your suggestion. Changing to desc works well.
For the second question, can I just have only one measure in which I can have to sub-measures?
From your measure, it seems not to work in my environment even when you set totalSales as a variable. totalSales seems not to passed to expression inside RANKX
@s15 remove the [ brackets i think you using a variable
Proud to be a Super User!
Thank you @vanessafvg
I make it a variable but now it seems not to be working. I think DESC with 2 is OK but somehow it doesn't filter two products.
@s15 can you bring the ranking back as well? ive picked up one issue and that is the
CALCULATE(SUM ( Sales[TotalCost] ))
need to have a calculate in front of it as all the ranking one will be 1.
i think the calculation is incorrect, need to check it out properly on video, will look at later
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
86 | |
84 | |
77 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |