Forum Discussion

collinq's avatar
collinq
Super User
5 years ago
Solved

Rankx Issue

Hi all, This is a bit embarrassing, but, frankly, I have now confused myself by trying so many variations .  I thought this would be easy but obviously I am missing something.  I want to rank my cus...
  • edhans's avatar
    edhans
    5 years ago

    Ok collinq - I am not seeing something. When I do a simple RANKX, I am getting some groupings I don't understand, and rank 8 is missing.

    I don't know why it is doing that. Perhaps someone a bit better at DAX would see the issue. TomMartens Greg_Deckler  perhaps.

    That issue aside, the following measure does rank based on the dates selected in the slicer (see file attached) but again with a few skipped items and a few grouped together.

    ALLSELECTED RankX = 
RANKX(
    ALLSELECTED(Margin),
    [Total Gross Margin]
)

Total Gross Margin = SUM(Margin[Gross Margin])

    For example, selecting April shows this:

    I cannot for the life of me figure out where ranked item #2 went. I know why #4 is repeated - same value, But not why #7 is repeated.

     

    Here is my PBIX file. All data is in it, no need to connect to outside files. I had to clean your data up a bit collinq - there was no customer 4661 in your customer table, so filtered that out, and got rid of all zeros and blanks for testing.

     

     

TomMartens's avatar
TomMartens
Super User
5 years ago

Hey edhans 

the answer to your question is not that simple, because of the use of ALLSELECTED 🙂

Using ALLSELECTED as table function removes/blocks all existing filters like April 2020, then the ranking happens across all customers. As there is
no data for the customer 10754 (customerid) in April 2020 rank 2 is missing. The same is valid for rank 8.

Your measure RANKX( ALLSELECTED (facttable) ) returns the lifetime ranking.

I have to admit that I do not fully understand the expected result. From the output table provided, I assume your measure creates the expected result.

Regards,
Tom

edhans's avatar
edhans
Community Champion
5 years ago

thanks Tom. Sometimes it is just tweaking where the filter is placed too.

collinq - after some tinkering, this works:

ALLSELECTED RankX = 
RANKX(
    ALLSELECTED(Customers[CustID]),
    [Total Gross Margin]
)

4 is missing below because there is a tie at 3. I reused my simple Total Gross Margin measure here. You could use it inside of the RANKX() as long as you wrap the SUM in CALCULATE if desired.