Summarize and Rankx issues

[Anonymous]

Scenario:

I have a poll data with lots and lots of columns.

I want to display a pie chart with top 5 (out of aprox 300) items by count of mentions for one specifc column, the rest has to be grouped in an "Others" bucket:

So what I did is to create a new table on Desktop:

TableTop5 = SUMMARIZE(Vox,Vox[Item],"Quantity",COUNTA(Vox[Item]))

Then added a column to the table TableTop5:

Top = IF(RANKX(ALLSELECTED(TableTop5),TableTop5[Quantity],,DESC,Skip)<=5,TableTop5[Item],"Others")

I am using slicers to filter key topics of the polls (Year, Region, City, etc.)

With the slicers set in "All", the results are perfect.

Now, the issues:

If I apply any filter (filters to table Vox) the total count in table TableTop5 is wrong, the count on the top 5 elements does not move.

Rank is always the same, it does not recalculate with any filter applied, the Rank is calculated and fixed for the 100% of the data. No matter what filter I do Rankx results is always the same. Even if I use a filter to select on the Item column. If I remove (filter out) item ranked in #2, the result is that now I have a top 4 and Others.

Thank you in advance for your ideas and input to resolve this issue.

With no

[BetterCallFrank]

Hi,

you could just try to move the RANKX calculation from a calc. column to a calc measure in the same table.

This will be re-evaluated every time you change a filter/slicer and will therefore always give you the correct results.

Does this help?

[Anonymous]

Hi, seems to be the way to go, but I am having trouble converting this column formula in order to work as a measure:

Top = IF(RANKX(ALLSELECTED(TableTop5),TableTop5[Quantity],,DESC,Skip)<=5,TableTop5[Item],"Others")

 Thank you in advance for helping me on this one.

[BetterCallFrank]

Luis, can you upload a PBIX file somewhere?

As for a generic solution the measure could look something like this:

Rank = 
VAR CurrQty = CALCULATE( SUMX( _Measures, _Measures[Quantity] ) )
RETURN
COUNTROWS( 
  FILTER( 
    ALL( DimProduct ), 
    CALCULATE( SUMX( _Measures, _Measures[Quantity] ) ) > CurrQty 
  )
) + 1

[v-huizhn-msft]

Hi @Anonymous.

When you rank the TableTop5[Quantity], it will calculated for all rows in resource data. The rank is same when you select different values in different slicer. You want to rank accoring to slicer, right? Based on the description, I am not able to reproduce the scenario, could you please share sample data for further analysis?

Best Regards,
Angelia