DAX or Measure to show top largest values

[s15]

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.

[vanessafvg]

@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

[s15]

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

[s15]

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

[vanessafvg · 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 ()
            )
        )
    )

[novicepbixer]

Thanks for this expression! Helped out a lot!

[FrankPower-001]

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

[s15]

@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

[vanessafvg]

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

[s15]

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.

[vanessafvg]

@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