Count duplicates RANK in a matrix visual column without Power Query

[Anonymous]

Hi

I have a very simple visual matrix on a report page with as first column Article names (than can be filtered with a slicer) and a second colum with their Total sales. I made a third column with the ranks on the total sales that works perfectly as expected!

However.... i need a fourth column in DAX that for every row shows the number of times a given rank repeats. I'm searching since a week for a solution but i failed.

Thx a lot for your help

[tamerj1]

Hi @Anonymous 
Here is a sample file with the proposed solution https://www.dropbox.com/t/3ndOIIvOfCavuLwa

# Identical Ranks = 
VAR CurrentAmount = [Total]
VAR T1 = ALLSELECTED ( Sales[Article] )
VAR T2 = ADDCOLUMNS ( T1, "@Total", [Total] )
VAR T3 = SUMMARIZE ( T2, [@Total], "@Count", DISTINCTCOUNT ( Sales[Article] ) )
VAR T4 = FILTER ( T3, [@Total] = CurrentAmount )
RETURN
    SELECTCOLUMNS ( T4, "@@Count", [@Count] )

[tamerj1]

@Anonymous 

Sorry my mistake. Just replace it with [Total] measure I will update the original reply

[tamerj1]

@Anonymous 

By the way the code in the file is just fine. Just download it

[Anonymous]

Why did you hardcode one of the value (the 12000) of the matrix in the formula? Does that mean if the the values differ and change over time or that they are more than one duplicate rank your formula won't work anymore?

[tamerj1]

@Anonymous 
I have updated the screenshot and the code. Actually I have taken the screenshot of a table I just created to track the code but the measure is there in the shared file and the matix is based on it otherwise the results would be wrong.

[SpartaBI]

@Anonymous Here is a link to download the file with the solution:
Count duplicates RANK in a matrix visual column without Power Query 2022-07-21.pbix

Please be kind with what I asked in the previous message 😀

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

I can't download any files at work. Can you share the DAX formula here in plain text on the forum please?

Be sure that if it works i will do a thumbs up next monday 😉

[SpartaBI · Accepted solution]

@Anonymous Why next monday :))
This is the formula:

# Identical Rank = 
VAR _tbl = 
    ADDCOLUMNS(
        ALLSELECTED('Table'[Article]),
        "@Rank", RANKX(ALLSELECTED('Table'[Article]), [# Total])
    )
VAR _current_rank = [Rank]
VAR _result = 
    COUNTROWS(
        FILTER(
            _tbl,
            [@Rank] = _current_rank
        )
    )
RETURN
    _result

And these are the other measure I created:

# Total = SUM('Table'[Total])

Rank = RANKX(ALLSELECTED('Table'[Article]), [# Total])

      

Showcase Report – Contoso By SpartaBI

[Anonymous]

Sorry to answer so late i was kinda sick. That solves my problem indeed. The solution is purrrfect.

Thx a lot for your help @SpartaBI 

[SpartaBI]

@Anonymous I need more info.
Can you share a sample file and explain exactly what is the desired result you want to get for that file (with few examples).

[Anonymous]

See below a minimal working example:

 

[SpartaBI]

@Anonymous ok, preparing something 😀 and will share soon a solution. 
While you wait will appreciate your Kudos on my messages 😄 and check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂