Forum Discussion
Count duplicates RANK in a matrix visual column without Power Query
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
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])
12 Replies
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 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 🙂
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 😀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])
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] )Anonymous
Sorry my mistake. Just replace it with [Total] measure I will update the original reply
Anonymous
By the way the code in the file is just fine. Just download it
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.
