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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors