cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbrandone
Helper III
Helper III

TOPN or RANKX help please

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.

NameOrder TypeLocationOutcome
John SmithOrderDomesticShipped
Jane DoeOrderDomesticShipped
Chris WilliamsTrialInternationalShipped
Jack RobertsOrderDomesticCancelled
Christina ConnellyTrialInternationalShipped
John SmithOrderDomesticShipped
Jane DoeOrderDomesticCancelled
Chris WilliamsTrialInternationalShipped
Jack RobertsTrialDomesticShipped
Christina ConnellyOrderInternationalCancelled
John SmithOrderDomesticShipped
Jane DoeOrderDomesticShipped
Chris WilliamsTrialInternationalShipped
Jack RobertsOrderDomesticCancelled
Christina ConnellyOrderInternationalShipped

 

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
1 REPLY 1
lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue, including the timestamp column.

Please indicate expected outcome.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!