TopN with Multiple Slicers not Working

BrettOlbrys1

Board Regular
Joined
May 1, 2018
Messages
139
Office Version
  1. 365
Platform
  1. Windows
TABLE1 is named ACTIVATIONS. ACTIVATIONS has an ACCOUNT column, a QUANTITY column, a SEGMENT column, an ORGANIZATION column, and a DATE column. ACTIVATIONS also has a SEGMENTID column, a REGIONID column, and a UNIQUEKEY column.

TABLE2 is named SEGMENTS, with a SEGMENT column and a SEGMENT ID column.

TABLE3 is named REGIONS, which has a REGIONID column, a REGIONNAME column, and a SEGMENTID column.

TABLE4 is named BRIDGETABLE, which has a BRIDGEID column, a REGIONID column, a SEGMENTID column, and a UNIQUEKEY column.

The relationships are set up through the bridge table (TABLE4):
SegmentID in the SEGMENT table to SegmentID in BRIDGETABLE
RegionID in the REGION table to RegionID in the BRIDGETABLE
UniqueKey in the ACTIVATIONS table to UniqueKey in BRIDGETABLE

I am using three slicers to filter the ACTIVATIONS data (TABLE1). The DATE slicer uses the DATE column from ACTIVATIONS, the SEGMENT slicer uses the SEGMENT column from ACTIVATIONS, and the REGION slicer uses the ORGANIZATION column from ACTIVATIONS.

Using any combination of the three slicers, I need to create a dynamic TOP 10 LIST showing the ACCOUNT in column one, the ORGANIZATION data in column 2, the QUANTITY in column 3, and the DATE in column 4. The list should adjust automatically based on the slicers and always show the Top 10 results.

The ACTIVATIONS table (TABLE1) has 500,000+ rows of data.

MY ISSUES

I created a TOP10ACCOUNTS table (TABLE5) with the following code:
Top10Accounts =
VAR SelectedData =
SUMMARIZE(
FILTER(
ACTIVATIONS,
ACTIVATIONS[DATE] IN VALUES(ACTIVATIONS[DATE]) &&
ACTIVATIONS[SEGMENT] IN VALUES(ACTIVATIONS[SEGMENT]) &&
ACTIVATIONS[ORGANIZATION] IN VALUES(ACTIVATIONS[ORGANIZATION])
),
ACTIVATIONS[ACCOUNT],
ACTIVATIONS[ORGANIZATION],
ACTIVATIONS[DATE],
"TotalQuantity", SUM(ACTIVATIONS[QUANTITY])
)
VAR RankedData =
ADDCOLUMNS(
SelectedData,
"Rank", RANKX(SelectedData, [TotalQuantity], , DESC, DENSE)
)
RETURN
FILTER(RankedData, [Rank] <= 10)

ISSUE: The TOP10ACCOUNTS table (TABLE5) correctly assigns a rank of 1 to 10 for the top 10 rows based on quantity, but it is based on the overall table and no filters, and the slicers are not dynamically affecting this table, so the table always shows the same top 10, which is wrong. If I use the fields from TABLE5 in my table visual, the visual does not change.

ISSUE: If I use the same fields, but from my ACTIVATIONS table, the slicers correctly change all of the data in the visual, but it is not limited to the TOP10.

ISSUE: If I use the RANKX function and limit it to <=10, it correctly shows the rows where the rank is 10 or less, BUT there are repeated ranks such as 1, 2, 3, 3, 4, 5, 6, 6, 6, 7, 8, 8, 9, 10, 10, which is 15 rows, BUT the rows that are ranked the same have DIFFERENT QUANTITIES (why are they getting the same rank?). I am using this formula:

AccountRank =
RANKX(
ALLSELECTED(ACTIVATIONS),
[TotalQuantity],
,
DESC,
DENSE
)

ISSUE: If I then try to multiply a column by .000000001 to see if that will provide me with proper rankings, I run out of resources.

Any thoughts about a possible solution to any of the above issues?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Forum statistics

Threads
1,223,886
Messages
6,175,195
Members
452,616
Latest member
intern444

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