BrettOlbrys1
Board Regular
- Joined
- May 1, 2018
- Messages
- 139
- Office Version
- 365
- Platform
- 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?
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?