Forum Discussion

Anaxielia's avatar
Anaxielia
Helper I
5 years ago
Solved

Ranking question

Hi,

 

I have a table that basicly contains that Branch Name, day, loaded amount to ATM.. 

 

Branch --- Trandate --- Amount

London --- 01/01/2021 --- 1000

London --- 02/01/2021 --- 1500

London --- 07/01/2021 --- 2500

Bristol --- 01/01/2021 --- 2000

Bristol --- 03/01/2021 --- 2000

London --- 10/01/2021 --- 1000

Liverpool --- 01/01/2021 --- 1500

Liverpool --- 05/01/2021 --- 5000

Liverpool --- 15/01/2021 --- 5000

 

I have a "BranchName" slicer on the page that filters out several visuals. I want to place a Card visual that shows selected Branch's rank by day count. For example, as in the table above, when i select "Liverpool" on the slicer, card should show the rank (number) of 2 because liverpool branch is the second most branch that loaded to ATM. (London is 1st by 4 days, Liverpool is 2nd by 3 days and Bristol is 3rd by 2 days)

 

I created a measure as below but with no success (when i select a branch in the slicer, measure card shows the rank of "1")

 

(M) ModelRank = RANKX(ALLSELECTED('ATM Detais'[BranchName]);CALCULATE(DISTINCTCOUNT('ATM Details'[Trandate])))
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anaxielia 

    You can use this measure where I included a condition to exclude zero rows

    ModelRank = 
RANKX(
    all('ATM Detais'[Branch ]),
    CALCULATE(
         DISTINCTCOUNT('ATM Detais'[ Trandate ]),
         'ATM Detais'[ Amount] <> 0,
         ALLEXCEPT('ATM Detais','ATM Detais'[Branch ])
    )
)



5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anaxielia , Try all inplace of allselected

    (M) ModelRank = RANKX(all('ATM Detais'[BranchName]);CALCULATE(DISTINCTCOUNT('ATM Details'[Trandate])))

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anaxielia 

    I modified your measure: Please find attached the PBIX file below my signature.

     

    ModelRank = 
RANKX(
    all('ATM Detais'[Branch ]),
    CALCULATE(
         DISTINCTCOUNT('ATM Detais'[ Trandate ]),
         ALLEXCEPT('ATM Detais','ATM Detais'[Branch ])
    )
)

     

    You can also use this measure in the table:

     

     

    • Anaxielia's avatar
      Anaxielia
      Helper I
      5 years ago

      Measure seems working but in my real time data, there is something wrong. As now, i saw that there are some rows that "Amount" cell is zero. this measure you suggested counts these "zero" rows as well? I tried to put on Filters panel this condition but it seems it didnt worked.. how can i exclude 0 amounted rows?

      • Fowmy's avatar
        Fowmy
        Super User
        5 years ago

        Anaxielia 

        You can use this measure where I included a condition to exclude zero rows

        ModelRank = 
RANKX(
    all('ATM Detais'[Branch ]),
    CALCULATE(
         DISTINCTCOUNT('ATM Detais'[ Trandate ]),
         'ATM Detais'[ Amount] <> 0,
         ALLEXCEPT('ATM Detais','ATM Detais'[Branch ])
    )
)