Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Summarize and Rankx issues

 

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

Need Help

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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


  • BetterCallFrank's avatar
    BetterCallFrank
    Resolver IV
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        BetterCallFrank
        Resolver IV
        9 years ago

        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