Forum Discussion

lucioxiii's avatar
lucioxiii
Copper Contributor
Oct 27, 2021

Pivot table - Use count function based on Ranking

Hi,   Is there a way in pivot table based on the ranking values to count the number of times an item has appeared in the top 5 for last 4 weeks?   I am trying to achieve the far right column belo...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 28, 2021

lucioxiii 

Perhaps I misunderstood. We speak about structured table or about PivotTable ? If PivotTable what is the source for it?

For the structured table it could be like

=LET(
  large5, LARGE([Rank for Week 1], SEQUENCE(5)),
  ranks,  INDEX( Table1, ROW() - ROW(Table1[#Headers]), {3,5,7,9}),
  SUM( IF( ISNA( XMATCH(ranks, large5) ), 0, 1)) )

but that doesn't meet your manually added column. Perhaps you may give bit more details.

lucioxiii's avatar
lucioxiii
Copper Contributor
Oct 28, 2021

SergeiBaklan 

 

I am working in a pivot table. So using the Pivot table ranking feature (as explained in this https://tanducits.com/tips-and-tricks/adding-rank-in-pivot-tables-in-excel#1 for 2010 versions and above). The pivot table sources is a table within the workbook.

 

Hope this is the information you were after.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 28, 2021

    lucioxiii 

    If that's PivotTable shown in initial post, when creating PivotTable add data to data model and play with explicit DAX measures instead of default implicit aggregation.  Basic is here Use of RANKX in Power BI measures - SQLBI, rest depends on concrete model.

    • lucioxiii's avatar
      lucioxiii
      Copper Contributor
      Oct 29, 2021
      Thank you, that helps with getting the Ranks however, how do I go about adding the column that will evaluate the ranks and count the number of times an item has appeared in top 5 for the last 4 weeks?

Resources