Help Me Solve this Problem

BrettOlbrys1

Board Regular
Joined
May 1, 2018
Messages
139
Office Version
  1. 365
Platform
  1. Windows
In POWERBI, I have an ACTIVATIONS table. ACTIVATIONS has an ACCOUNT column (column A), a SEGMENT column (column B), and a QTY column (column C). There are 500,000+ rows of data.

Let's use this example:

In column A (ACCOUNT), I have these 20 rows of data: A, B, C, D, E, F, G, H, I, J, A, B, C, D, E, F, G, H, I, J.
In column B (SEGMENT), I have these 20 rows of data: ENT, GOV, SMB, ENT, GOV, SMB, STA, ENT, GOV, SMB, STA, OTH, SMB, ENT, GOV, SMB, STA, OTH, GOV, SMB.
In column C (QTY), I have these 20 rows of data: 79, 181, 92, 200, 40, 80, 7, 223, 300, 98, 42, 240, 100, 11, 38, 90, 200, 179, 61, 200.

If I create a TOP10 table visual with ACCOUNT (column A) and QTY (column C), I get the following result in my table visual:

The first column is B, H, I, J, D, G, C, F, A, E.
The second column is 421, 402, 361, 298, 211, 207, 192, 170, 121, 78.

The result is correct because it lists the quantity from greatest to least for the accounts and only shows ten rows of data.

But, if I introduce the SEGMENT column into my table visual, the TOP10 table now shows the following:

The first column is I, J, B, H, D, G, C, B, H, F, A, E, A.
The second column is 361, 298, 240, 223, 211, 207, 192, 181, 179, 170, 79, 78, 42.
The third column is GOV, SMB, OTH, ENT, ENT, STA, SMB, GOV, OTH, SMB, ENT, GOV, STA.

Instead of 10 rows of data, I now have 13 rows because if an ACCOUNT from column A had two different values from the SEGMENT column (column C), the visual included both rows.

I only want the top 10 rows of data, even if a row is split. So, in the above example, my result should be:

The first column is I, J, B, H, D, G, C, B, H, F.
The second column is 361, 298, 240, 223, 211, 207, 192, 181, 179, 170.
The third column is GOV, SMB, OTH, ENT, ENT, STA, SMB, GOV, OTH, SMB.

How do I get a table visual with ONLY 10 ROWS of DATA?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,223,932
Messages
6,175,468
Members
452,646
Latest member
tudou

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top