Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

A new Data Days event is coming soon! This time we’re going bigger than ever. Fabric, Power BI, SQL, AI and more. Don't miss out.

Reply
derekli1700
Helper III
Helper III

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

‎05-06-2025 05:56 AM

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 

 

CustomerDateStoreSales
Customer JamesMar-24Store A4480.36
Customer AliceOct-24Store A5394.22
Customer JohnOct-24Store A7219.71
Customer MariaMay-24Store A3899.56
Customer DavidJul-24Store A3778.43
Customer EmmaJan-25Store A4944.29
Customer RobertMar-25Store A4376.81
Customer OliviaAug-24Store A591.45
Customer MichaelMay-24Store A1172.64
Customer SophiaJan-25Store A2887.87
Customer JamesJul-24Store B1113.83
Customer AliceApr-25Store B1378.43
Customer JohnApr-24Store B3352.6
Customer MariaMay-24Store B858.31
Customer DavidAug-24Store B48.68
Customer EmmaNov-24Store B5978.15
Customer RobertFeb-25Store B6894.36
Customer OliviaDec-24Store B4820.88
Customer MichaelMar-25Store B985.74
Customer SophiaJul-24Store B86.48
Customer JamesMay-24Store C3949.82
Customer AliceFeb-25Store C7689.93
Customer JohnNov-24Store C6416.19
Customer MariaJan-25Store C771.42
Customer DavidJan-24Store C151.33
Customer EmmaJul-24Store C1730.4
Customer RobertJan-24Store C1159.39
Customer OliviaApr-25Store C542.94
Customer MichaelJul-24Store C3223.19
Customer SophiaMay-24Store C3533.04
Customer JamesMar-24Store D4976.63
Customer AliceJan-24Store D4437.71
Customer JohnMay-24Store D4644.92
Customer MariaMay-24Store D124.97
Customer DavidJan-25Store D3141.11
Customer EmmaJun-24Store D5476.17
Customer RobertJan-25Store D5592.62
Customer OliviaJan-25Store D5026.75
Customer MichaelDec-24Store D1023.91
Customer SophiaJan-24Store D1214.96
Customer JamesFeb-24Store E5030.58
Customer AliceSep-24Store E6634.67
Customer JohnJul-24Store E1091.54
Customer MariaJun-24Store E5266.43
Customer DavidJul-24Store E6898.79
Customer EmmaJun-24Store E3619.01
Customer RobertJul-24Store E1258.28
Customer OliviaDec-24Store E7365.55
Customer MichaelSep-24Store E1345.84
Customer SophiaAug-24Store E6802.67
Customer JamesMar-24Store F2978.62
Customer AliceDec-24Store F4656.92
Customer JohnAug-24Store F5960.03
Customer MariaFeb-24Store F4624.55
Customer DavidMay-24Store F1412.48
Customer EmmaSep-24Store F3132.18
Customer RobertJan-25Store F3320.64
Customer OliviaJun-24Store F1532.88
Customer MichaelAug-24Store F7336.87
Customer SophiaNov-24Store F6966.52
Customer JamesMar-24Store G7175.25
Customer AliceSep-24Store G2768.67
Customer JohnJan-24Store G2199.26
Customer MariaSep-24Store G4941.78
Customer DavidMay-24Store G5308.58
Customer EmmaDec-24Store G896.19
Customer RobertApr-25Store G4321.03
Customer OliviaMar-24Store G1177.22
Customer MichaelApr-25Store G2972.99
Customer SophiaMay-24Store G2991.99
Customer JamesJul-24Store H3891.92
Customer AliceFeb-25Store H4891.37
Customer JohnApr-24Store H4505.88
Customer MariaDec-24Store H1314.21
Customer DavidJul-24Store H3853.79
Customer EmmaJan-24Store H3281.06
Customer RobertNov-24Store H5383.79
Customer OliviaSep-24Store H3717.01
Customer MichaelNov-24Store H2556.75
Customer SophiaAug-24Store H5794.92
Customer JamesFeb-24Store I304.85
Customer AliceJan-25Store I2840.59
Customer JohnOct-24Store I1739.81
Customer MariaSep-24Store I2365.56
Customer DavidFeb-25Store I2822.94
Customer EmmaApr-25Store I5046.16
Customer RobertOct-24Store I7865.46
Customer OliviaAug-24Store I6782.78
Customer MichaelJun-24Store I215.07
Customer SophiaMar-25Store I3325.57
Customer JamesApr-24Store J735.51
Customer AliceSep-24Store J5530.28
Customer JohnJan-24Store J5262.34
Customer MariaMar-24Store J3517.74
Customer DavidAug-24Store J3540.72
Customer EmmaFeb-24Store J4405.01
Customer RobertNov-24Store J3923.77
Customer OliviaSep-24Store J6587.08
Customer MichaelAug-24Store J156.66
Customer SophiaJul-24Store J4641.71
Customer JamesMay-24Store K2146.56
Customer AliceApr-24Store K6604.09
Customer JohnJan-24Store K4014.84
Customer MariaNov-24Store K564.81
Customer DavidFeb-25Store K2002.09
Customer EmmaJun-24Store K4542.44
Customer RobertMar-24Store K2833.73
Customer OliviaJun-24Store K5156.17
Customer MichaelNov-24Store K3590.82
Customer SophiaMar-25Store K4363.1
Customer JamesFeb-25Store L5376.64
Customer AliceFeb-24Store L2084.75
Customer JohnApr-24Store L6899.38
Customer MariaJan-25Store L1202.27
Customer DavidAug-24Store L6053.43
Customer EmmaAug-24Store L802.67
Customer RobertMar-25Store L2571.64
Customer OliviaJul-24Store L5042.49
Customer MichaelMar-24Store L7451.81
Customer SophiaJun-24Store L4588.15
Customer JamesMay-24Store M1146.35
Customer AliceJun-24Store M219.15
Customer JohnMar-24Store M56.29
Customer MariaAug-24Store M5913.1
Customer DavidNov-24Store M7780.34
Customer EmmaFeb-25Store M6077.38
Customer RobertJul-24Store M2158.66
Customer OliviaJan-24Store M5070.03
Customer MichaelJan-25Store M268.38
Customer SophiaApr-25Store M4545.06
Customer JamesDec-24Store N5780.7
Customer AliceAug-24Store N3317.67
Customer JohnMar-25Store N1382.03
Customer MariaApr-25Store N1316.55
Customer DavidJan-24Store N6834.33
Customer EmmaMar-25Store N1199.66
Customer RobertApr-25Store N5157.43
Customer OliviaSep-24Store N4759.64
Customer MichaelFeb-25Store N2769.05
Customer SophiaMar-24Store N3360.83
Customer JamesJan-25Store O2269.98
Customer AliceJan-25Store O1683.08
Customer JohnSep-24Store O1043.49
Customer MariaApr-25Store O6268.22
Customer DavidAug-24Store O3061.61
Customer EmmaApr-25Store O4605.1
Customer RobertFeb-25Store O4518.66
Customer OliviaJul-24Store O7532.97
Customer MichaelNov-24Store O5555.57
Customer SophiaMay-24Store O4201.57
Labels:
Message 1 of 4
810 Views
1 ACCEPTED SOLUTION
OktayPamuk80
Responsive Resident
Responsive Resident

‎05-06-2025 12:40 PM

Hi,

I guess, you are looking for something like this:

OktayPamuk80_0-1746560126052.png

 

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 😉

View solution in original post

Message 2 of 4
771 Views
0
3 REPLIES 3
Anonymous
Not applicable

‎05-10-2025 09:57 PM

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

Message 4 of 4
700 Views
OktayPamuk80
Responsive Resident
Responsive Resident

‎05-06-2025 12:40 PM

Hi,

I guess, you are looking for something like this:

OktayPamuk80_0-1746560126052.png

 

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 😉

Message 2 of 4
772 Views
0
Kimtran
Regular Visitor
In response to OktayPamuk80

‎05-06-2025 01:32 PM

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

Message 3 of 4
747 Views
0

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All