I cannot get either TOPN or RANKX to show the data that I need. It either does not show correctly with the Top 10 or Bottom 10 that I need or if I use RANKX first, I get "1" for all "Names". I deal with sensitive data so I created a mock data table that has similar columns that I show in my dataset.
I need to show the Top 10 and Bottom 10 "Customers" based on a specific time period that can be filtered by the end user. The main table that I have has an "Order request" per row with date of order, location of order, order type and whether it was finalized or not ("Shipped"). My dataset has 100's of customers both Domestically and Internationally. I have the above mentioned Order request table along with a fact table with all Customers and their location. Below is an example of the Order Request table with the columns that I need to filter by.
| Name | Order Type | Location | Outcome |
| John Smith | Order | Domestic | Shipped |
| Jane Doe | Order | Domestic | Shipped |
| Chris Williams | Trial | International | Shipped |
| Jack Roberts | Order | Domestic | Cancelled |
| Christina Connelly | Trial | International | Shipped |
| John Smith | Order | Domestic | Shipped |
| Jane Doe | Order | Domestic | Cancelled |
| Chris Williams | Trial | International | Shipped |
| Jack Roberts | Trial | Domestic | Shipped |
| Christina Connelly | Order | International | Cancelled |
| John Smith | Order | Domestic | Shipped |
| Jane Doe | Order | Domestic | Shipped |
| Chris Williams | Trial | International | Shipped |
| Jack Roberts | Order | Domestic | Cancelled |
| Christina Connelly | Order | International | Shipped |
Like I said, I have thousands of order dating back several years and the request offer table will have multiple rows per customer for their multiple orders. To save space on the report, I would like to have a slicer based on the "Location" column which will switch the Top 10 and Bottom 10 from Domestic and International customers. That is part of the issue I have had is to have a Rank or Top 10 for Domestic only and then the same for International, but their data is mixed in the same table.
I have tried:
Top10 Customers =
CALCULATE(
RequestOffer[Total Order Count Scheduled],
TOPN(
10,
ALL(Contacts[Name_and_Rank]),
RequestOffer[Total Order Count Scheduled],
DESC
),
VALUES(Contacts[Name_and_Rank])
)
and:
Customer Rank =
VAR DomRank = RANKX(
ALL(RequestOffer[Name]),
CALCULATE(
COUNTROWS(RequestOffer),
RequestOffer[Status] = "Shipped",
RequestOffer[RequestType] = "Order",
RequestOffer[Surgeon Location] = "Domestic"
),
,
DESC,
Dense
)
VAR IntRank = RANKX(
ALL(RequestOffer[Name]),
CALCULATE(
COUNTROWS(RequestOffer),
RequestOffer[Status] = "Shipped",
RequestOffer[RequestType] = "Order",
RequestOffer[Surgeon Location] = "International"
),
,
DESC,
Dense
)
VAR Result = IF(
VALUES(Contacts[Location Outcome]) = "Domestic",
DomRank,
IF(
VALUES(Contacts[Location Outcome]) = "International",
IntRank
)
)
Return Result
The Contacts and RequestOffer table are connected via relationship with CustomerId
Let me know if you need any additional information. I have been working on this for over a week and nothing seems to get me what I need
