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.

     

     

edhans's avatar
edhans
Community Champion
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.

       

  • collinq's avatar
    collinq
    Super User
    5 years ago

    Hi edhans  TomMartens  and wdx223_Daniel 

     

    Thank you all for the effort and time to help me with this one.  I truly appreciate it.