How to show Top and Bottom 20 Stores/Top 5 customers in a Matrix table

[derekli1700]

Hi - below is my sample datasheet, 
I want to know how to have a matrix table show only the top and bottom 20 Store's based off Sales and then dropdown so i can see their top 5 customers. So Store->Customer as the two rows and Sales as the value. Ideally when i select a time period on a slicer - it shows the top and bottom 20 stores, their Sales and then the 5 customer's sales contribution. Thanks!

Would appreciate a sample file being made - none of the top n videos/articles show how to do it for multiple rows 

Customer	Date	Store	Sales
Customer James	Mar-24	Store A	4480.36
Customer Alice	Oct-24	Store A	5394.22
Customer John	Oct-24	Store A	7219.71
Customer Maria	May-24	Store A	3899.56
Customer David	Jul-24	Store A	3778.43
Customer Emma	Jan-25	Store A	4944.29
Customer Robert	Mar-25	Store A	4376.81
Customer Olivia	Aug-24	Store A	591.45
Customer Michael	May-24	Store A	1172.64
Customer Sophia	Jan-25	Store A	2887.87
Customer James	Jul-24	Store B	1113.83
Customer Alice	Apr-25	Store B	1378.43
Customer John	Apr-24	Store B	3352.6
Customer Maria	May-24	Store B	858.31
Customer David	Aug-24	Store B	48.68
Customer Emma	Nov-24	Store B	5978.15
Customer Robert	Feb-25	Store B	6894.36
Customer Olivia	Dec-24	Store B	4820.88
Customer Michael	Mar-25	Store B	985.74
Customer Sophia	Jul-24	Store B	86.48
Customer James	May-24	Store C	3949.82
Customer Alice	Feb-25	Store C	7689.93
Customer John	Nov-24	Store C	6416.19
Customer Maria	Jan-25	Store C	771.42
Customer David	Jan-24	Store C	151.33
Customer Emma	Jul-24	Store C	1730.4
Customer Robert	Jan-24	Store C	1159.39
Customer Olivia	Apr-25	Store C	542.94
Customer Michael	Jul-24	Store C	3223.19
Customer Sophia	May-24	Store C	3533.04
Customer James	Mar-24	Store D	4976.63
Customer Alice	Jan-24	Store D	4437.71
Customer John	May-24	Store D	4644.92
Customer Maria	May-24	Store D	124.97
Customer David	Jan-25	Store D	3141.11
Customer Emma	Jun-24	Store D	5476.17
Customer Robert	Jan-25	Store D	5592.62
Customer Olivia	Jan-25	Store D	5026.75
Customer Michael	Dec-24	Store D	1023.91
Customer Sophia	Jan-24	Store D	1214.96
Customer James	Feb-24	Store E	5030.58
Customer Alice	Sep-24	Store E	6634.67
Customer John	Jul-24	Store E	1091.54
Customer Maria	Jun-24	Store E	5266.43
Customer David	Jul-24	Store E	6898.79
Customer Emma	Jun-24	Store E	3619.01
Customer Robert	Jul-24	Store E	1258.28
Customer Olivia	Dec-24	Store E	7365.55
Customer Michael	Sep-24	Store E	1345.84
Customer Sophia	Aug-24	Store E	6802.67
Customer James	Mar-24	Store F	2978.62
Customer Alice	Dec-24	Store F	4656.92
Customer John	Aug-24	Store F	5960.03
Customer Maria	Feb-24	Store F	4624.55
Customer David	May-24	Store F	1412.48
Customer Emma	Sep-24	Store F	3132.18
Customer Robert	Jan-25	Store F	3320.64
Customer Olivia	Jun-24	Store F	1532.88
Customer Michael	Aug-24	Store F	7336.87
Customer Sophia	Nov-24	Store F	6966.52
Customer James	Mar-24	Store G	7175.25
Customer Alice	Sep-24	Store G	2768.67
Customer John	Jan-24	Store G	2199.26
Customer Maria	Sep-24	Store G	4941.78
Customer David	May-24	Store G	5308.58
Customer Emma	Dec-24	Store G	896.19
Customer Robert	Apr-25	Store G	4321.03
Customer Olivia	Mar-24	Store G	1177.22
Customer Michael	Apr-25	Store G	2972.99
Customer Sophia	May-24	Store G	2991.99
Customer James	Jul-24	Store H	3891.92
Customer Alice	Feb-25	Store H	4891.37
Customer John	Apr-24	Store H	4505.88
Customer Maria	Dec-24	Store H	1314.21
Customer David	Jul-24	Store H	3853.79
Customer Emma	Jan-24	Store H	3281.06
Customer Robert	Nov-24	Store H	5383.79
Customer Olivia	Sep-24	Store H	3717.01
Customer Michael	Nov-24	Store H	2556.75
Customer Sophia	Aug-24	Store H	5794.92
Customer James	Feb-24	Store I	304.85
Customer Alice	Jan-25	Store I	2840.59
Customer John	Oct-24	Store I	1739.81
Customer Maria	Sep-24	Store I	2365.56
Customer David	Feb-25	Store I	2822.94
Customer Emma	Apr-25	Store I	5046.16
Customer Robert	Oct-24	Store I	7865.46
Customer Olivia	Aug-24	Store I	6782.78
Customer Michael	Jun-24	Store I	215.07
Customer Sophia	Mar-25	Store I	3325.57
Customer James	Apr-24	Store J	735.51
Customer Alice	Sep-24	Store J	5530.28
Customer John	Jan-24	Store J	5262.34
Customer Maria	Mar-24	Store J	3517.74
Customer David	Aug-24	Store J	3540.72
Customer Emma	Feb-24	Store J	4405.01
Customer Robert	Nov-24	Store J	3923.77
Customer Olivia	Sep-24	Store J	6587.08
Customer Michael	Aug-24	Store J	156.66
Customer Sophia	Jul-24	Store J	4641.71
Customer James	May-24	Store K	2146.56
Customer Alice	Apr-24	Store K	6604.09
Customer John	Jan-24	Store K	4014.84
Customer Maria	Nov-24	Store K	564.81
Customer David	Feb-25	Store K	2002.09
Customer Emma	Jun-24	Store K	4542.44
Customer Robert	Mar-24	Store K	2833.73
Customer Olivia	Jun-24	Store K	5156.17
Customer Michael	Nov-24	Store K	3590.82
Customer Sophia	Mar-25	Store K	4363.1
Customer James	Feb-25	Store L	5376.64
Customer Alice	Feb-24	Store L	2084.75
Customer John	Apr-24	Store L	6899.38
Customer Maria	Jan-25	Store L	1202.27
Customer David	Aug-24	Store L	6053.43
Customer Emma	Aug-24	Store L	802.67
Customer Robert	Mar-25	Store L	2571.64
Customer Olivia	Jul-24	Store L	5042.49
Customer Michael	Mar-24	Store L	7451.81
Customer Sophia	Jun-24	Store L	4588.15
Customer James	May-24	Store M	1146.35
Customer Alice	Jun-24	Store M	219.15
Customer John	Mar-24	Store M	56.29
Customer Maria	Aug-24	Store M	5913.1
Customer David	Nov-24	Store M	7780.34
Customer Emma	Feb-25	Store M	6077.38
Customer Robert	Jul-24	Store M	2158.66
Customer Olivia	Jan-24	Store M	5070.03
Customer Michael	Jan-25	Store M	268.38
Customer Sophia	Apr-25	Store M	4545.06
Customer James	Dec-24	Store N	5780.7
Customer Alice	Aug-24	Store N	3317.67
Customer John	Mar-25	Store N	1382.03
Customer Maria	Apr-25	Store N	1316.55
Customer David	Jan-24	Store N	6834.33
Customer Emma	Mar-25	Store N	1199.66
Customer Robert	Apr-25	Store N	5157.43
Customer Olivia	Sep-24	Store N	4759.64
Customer Michael	Feb-25	Store N	2769.05
Customer Sophia	Mar-24	Store N	3360.83
Customer James	Jan-25	Store O	2269.98
Customer Alice	Jan-25	Store O	1683.08
Customer John	Sep-24	Store O	1043.49
Customer Maria	Apr-25	Store O	6268.22
Customer David	Aug-24	Store O	3061.61
Customer Emma	Apr-25	Store O	4605.1
Customer Robert	Feb-25	Store O	4518.66
Customer Olivia	Jul-24	Store O	7532.97
Customer Michael	Nov-24	Store O	5555.57
Customer Sophia	May-24	Store O	4201.57

[v-bmanikante]

Hi @derekli1700 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[OktayPamuk80 · Accepted solution]

Hi,

I guess, you are looking for something like this:

For this, you need to create following measures:

Total Store Sales = CALCULATE(SUM('Sheet1'[Sales]), ALLEXCEPT('Sheet1', 'Sheet1'[Store]))

Store Rank = RANKX(ALL('Sheet1'[Store]), [Total Store Sales],,DESC, Dense)

Top & Bottom Stores Filter = IF([Store Rank] <= 20 || [Store Rank] > MAXX(ALL('Sheet1'[Store]), [Store Rank]) - 20, 1, 0)

Customer Rank by Store = RANKX(FILTER(ALL('Sheet1'), 'Sheet1'[Store] = MAX('Sheet1'[Store])),
CALCULATE(SUM('Sheet1'[Sales])),,DESC)

Top 5 Customers per Store = IF([Customer Rank by Store] <= 5, 1, 0)

Then, build the Matrix:

• Drag Store and then Customer into the Rows section.

• Add the measure Total Store Sales to the Values section.

• Apply the filters on the visual for Top & Bottom Stores Filter and Top 5 Customers per Store by setting them to 1.

Hope this helps.

Regards,

Oktay

If it helped you, would be happy for a Kudos and mark the answer as a solution 😉

[Kimtran]

hi, i didn't ask this question but i was looking for a similar solution, thank you for your reply. Just want to say this part doesn't work by the way: Top & Bottom Stores Filter = IF([Store Rank] <= 20 || [Store Rank] > MAXX(ALL('Sheet1'[Store]), [Store Rank]) - 20, 1, 0)

I think it should be: MAXA('Table'[Store Rank]) to give the largest number in the rank