Oct 27 2021 04:29 PM
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 1 | Rank for Week 1 | Week2 | Rank in Week 2 | Week3 | Rank in Week 3 | Week4 | Rank in Week 4 | Times appeared in top 5 in 4 weeks | |
Item 1 | 881 | 9 | 632 | 7 | 656 | 9 | 491 | 3 | 1 |
Item 2 | 556 | 8 | 326 | 4 | 464 | 7 | 755 | 6 | 1 |
Item 3 | 357 | 5 | 839 | 10 | 394 | 6 | 836 | 8 | 0 |
Item 4 | 403 | 6 | 271 | 3 | 214 | 3 | 879 | 9 | 2 |
Item 5 | 891 | 10 | 9 | 1 | 977 | 10 | 689 | 4 | 2 |
Item 6 | 156 | 3 | 10 | 2 | 373 | 5 | 826 | 7 | 2 |
Item 7 | 122 | 2 | 336 | 5 | 335 | 4 | 200 | 1 | 3 |
Item 8 | 209 | 4 | 764 | 9 | 110 | 2 | 214 | 2 | 3 |
Item 9 | 460 | 7 | 655 | 8 | 76 | 1 | 730 | 5 | 1 |
Item 10 | 117 | 1 | 442 | 6 | 578 | 8 | 936 | 10 | 1 |
Thanks in advance
Oct 28 2021 12:11 PM
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.
Oct 28 2021 01:53 PM
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.
Oct 28 2021 02:08 PM
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.
Oct 28 2021 05:14 PM