Ranking with row counts

[TexasBI]

Hello,

I have tried everywhere and everywhich way but I can not get ranking to work when using COUNT. Maybe data looks something like this:

Item

A

B

C

A

C

A

C

Then when you put it into a table in Power BI with one column being the [Item] and the other as Count of [Item] you would get something like this..

Item	Item Count
A	3
B	1
C	3

Then once I add this RANKX measure I get nothing but 1s:

RANKX (
       ALLSELECTED(exampleTable)

       CALCULATE(COUNT( [Item] )

       , , DESC , DENSE
)

What is going on and what am I doing wrong?

[Greg_Deckler · Accepted solution]

@TexasBI - Yep, replacing ALLSELECTED with ALL works.

[TexasBI]

Hey @Greg_Deckler ,

That did not work. Here is my code and here is my result.

_testRank =

RANKX(

ALLSELECTED(CUSTOMER_FEEDBACK),

CALCULATE(COUNTROWS(CUSTOMER_FEEDBACK)),,DESC,Dense

)

The source table has +1K rows. Each row is a single customer feedback. Each row has an assigned feedback category. I am trying to do a running total on the aggregated count for each category so i can build a pareto chart. Since some categories have the exact same count i can not use RANKX.

[Greg_Deckler]

@TexasBI - It is hard to say. Take a look at Table (3) and the associated visual of the attached PBIX file.  I took your sample data and just created this measure:

Rank = RANKX(ALL('Table (3)'),CALCULATE(COUNT([Item])),,DESC,Dense)

[TexasBI]

Hey @Greg_Deckler  thank you for putting that together. I think I may have confused everyone when needing a rank. I was hoping to use RANKX but once you have duplicate values, then you get duplicate ranks which throws your ability to calculate a running total off for a Pareto chart. 

What I need is to be able to do a running total on that count measure column and also be dynamic so when I filter the source table by different variables, the running total maeasure runs based off that count measure. I hope that makes sense?

[Greg_Deckler]

@TexasBI - Replace ALLSELECTED with ALL I would think. Try that while I test it out. Also, see To *Bleep* with RANKX, you may not have to use it but now you understand how that quick measure was named!!