Pivot table - Use count function based on Ranking

%3CLINGO-SUB%20id%3D%22lingo-sub-2893225%22%20slang%3D%22en-US%22%3EPivot%20table%20-%20Use%20count%20function%20based%20on%20Ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2893225%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20in%20pivot%20table%20based%20on%20the%20ranking%20values%20to%20count%20the%20number%20of%20times%20an%20item%20has%20appeared%20in%20the%20top%205%20for%20last%204%20weeks%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20achieve%20the%20far%20right%20column%20below%20in%20excel%20pivot%20table.%20Is%20this%20possible%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22961%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EWeek%201%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3ERank%20for%20Week%201%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3EWeek2%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3ERank%20in%20Week%202%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EWeek3%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3ERank%20in%20Week%203%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3EWeek4%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3ERank%20in%20Week%204%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E%3CSTRONG%3ETimes%20appeared%20in%20top%205%20in%204%20weeks%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%201%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E881%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E632%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E656%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E491%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%202%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E556%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E326%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E464%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E755%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%203%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E357%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E839%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E394%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E836%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%204%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E403%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E271%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E214%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E879%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%205%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E891%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E977%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E689%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%206%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E156%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E373%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E826%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%207%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E122%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E336%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E335%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E200%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%208%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E209%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E764%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E9%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E110%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E214%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%209%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E460%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E7%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E655%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E76%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E730%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E5%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2263px%22%3EItem%2010%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E117%3C%2FTD%3E%3CTD%20width%3D%22114px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2293px%22%3E442%3C%2FTD%3E%3CTD%20width%3D%22102px%22%3E6%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E578%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E8%3C%2FTD%3E%3CTD%20width%3D%2264px%22%3E936%3C%2FTD%3E%3CTD%20width%3D%2299px%22%3E10%3C%2FTD%3E%3CTD%20width%3D%22233px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2893225%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2897136%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20-%20Use%20count%20function%20based%20on%20Ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897136%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1194672%22%20target%3D%22_blank%22%3E%40lucioxiii%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExcel%20365%20Pro%20Plus%20with%20Power%20Pivot%20and%20Power%20Query.%3C%2FP%3E%3CP%3ECount%20top%205%20ranks%20in%204%20weeks.%3C%2FP%3E%3CP%3EWith%20PQ%20Index()%2C%20PP%20Related()%20and%20HasOneValue()%3C%2FP%3E%3CP%3EWith%20PivotChart.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mediafire.com%2Ffile%2Foqtkweowitq4ybk%2F10_28_21.xlsx%2Ffile%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mediafire.com%2Ffile%2Foqtkweowitq4ybk%2F10_28_21.xlsx%2Ffile%3C%2FA%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.mediafire.com%2Ffile%2Fwrrw519mamj2mk4%2F10_28_21.pdf%2Ffile%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.mediafire.com%2Ffile%2Fwrrw519mamj2mk4%2F10_28_21.pdf%2Ffile%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2897267%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20-%20Use%20count%20function%20based%20on%20Ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897267%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1194672%22%20target%3D%22_blank%22%3E%40lucioxiii%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20I%20misunderstood.%20We%20speak%20about%20structured%20table%20or%20about%20PivotTable%20%3F%20If%20PivotTable%20what%20is%20the%20source%20for%20it%3F%3C%2FP%3E%0A%3CP%3EFor%20the%20structured%20table%20it%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20large5%2C%20LARGE(%5BRank%20for%20Week%201%5D%2C%20SEQUENCE(5))%2C%0A%20%20ranks%2C%20%20INDEX(%20Table1%2C%20ROW()%20-%20ROW(Table1%5B%23Headers%5D)%2C%20%7B3%2C5%2C7%2C9%7D)%2C%0A%20%20SUM(%20IF(%20ISNA(%20XMATCH(ranks%2C%20large5)%20)%2C%200%2C%201))%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20that%20doesn't%20meet%20your%20manually%20added%20column.%20Perhaps%20you%20may%20give%20bit%20more%20details.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2897757%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20-%20Use%20count%20function%20based%20on%20Ranking%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2897757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EI%20am%20working%20in%20a%20pivot%20table.%20So%20using%20the%20Pivot%20table%20ranking%20feature%20(as%20explained%20in%20this%20%3CA%20href%3D%22https%3A%2F%2Ftanducits.com%2Ftips-and-tricks%2Fadding-rank-in-pivot-tables-in-excel%231%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Elink%3C%2FA%3E%26nbsp%3Bfor%202010%20versions%20and%20above).%26nbsp%3BThe%20pivot%20table%20sources%20is%20a%20table%20within%20the%20workbook.%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20data-unlink%3D%22true%22%3EHope%20this%20is%20the%20information%20you%20were%20after.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 below in excel pivot table. Is this possible?

 

 Week 1Rank for Week 1Week2Rank in Week 2Week3Rank in Week 3Week4Rank in Week 4Times appeared in top 5 in 4 weeks
Item 188196327656949131
Item 255683264464775561
Item 3357583910394683680
Item 440362713214387992
Item 589110919771068942
Item 61563102373582672
Item 712223365335420013
Item 820947649110221423
Item 94607655876173051
Item 10117144265788936101

 

Thanks in advance

5 Replies

@lucioxiii 

Excel 365 Pro Plus with Power Pivot and Power Query.

Count top 5 ranks in 4 weeks.

With PQ Index(), PP Related() and HasOneValue()

With PivotChart.

https://www.mediafire.com/file/oqtkweowitq4ybk/10_28_21.xlsx/file

https://www.mediafire.com/file/wrrw519mamj2mk4/10_28_21.pdf/file

 

 

@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.

@Sergei Baklan 

 

I am working in a pivot table. So using the Pivot table ranking feature (as explained in this link for 2010 versions and above). The pivot table sources is a table within the workbook.

 

Hope this is the information you were after.

@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.

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?