Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
s15
Helper III
Helper III

DAX or Measure to show top largest values

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.

1 ACCEPTED SOLUTION

@s15

 

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 ()
            )
        )
    )





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
vanessafvg
Super User
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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I know I can rank by using filter but I'd like to learn more from DAX.

 

ranking.PNG

Thank you @vanessafvg

I followed the measure in the video but the ranking is not correct.

 

sales.PNG

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

 

 

@s15

 

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 ()
            )
        )
    )





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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

 

total.PNG

 

@s15 remove the [ brackets i think you using a variable





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

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.

 

somehow.PNG

@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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.