Ranking question

[Anaxielia]

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

[Fowmy]

@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]

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 · Accepted solution]

@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 ])
    )
)

[Anaxielia]

Thank you! now it works like charm.. Most appreciated. Marked as solution..

[amitchandak]

@Anaxielia , Try all inplace of allselected

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