Help with RANKX

[harsh_bi_dev]

Hello everyone,

I am facing an issue with the RANKX DAX.

My requirement is to get best performing months year based on the total sales for each consultant with their respective locations and then I need to display TOP 50 records from this recordset based on Total Sales.

I am able to get best performing month year for each consultant with their respective locations by using RANKX function. Now I want to get only TOP 50 records from this record set in my table.

Below is my DAX to get best performing month year:

Rank Month Year = 
RANKX(
    CALCULATETABLE(
        VALUES('T1'[Month Year]),
        ALLSELECTED('T1'[Month Year])
    ),
    [Total Sales],,
    DESC,
    Dense
)

I am applying visual level filter that value of this measure should be 1. By this way I am getting the best performing month.

Can anyone help me out with how I can get TOP 50 records based on Total Sales after getting all the best performing months?

Thanks in advance

[v-stephen-msft · Accepted solution]

Hi @harsh_bi_dev ,

You can create a new measure as 

Measure = IF([Rank Month Year]<=50,1)

Then put this measure into Filters, and set show items when the value is 1

You can see the simple sample from the attachment.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@harsh_bi_dev 

You can incorporate with IF function if you are showing the measure against each consultants

=IF ( [Rank Month Year] <= 50 , [TOTAL SALES] )

[harsh_bi_dev]

Hello @Fowmy,

Thanks for the quick reply. But it will not solve my problem. I want TOP 50 groups of Location, Consultants and Month Year.

Hope this makes sense.