Forum Discussion

superlangford's avatar
superlangford
Regular Visitor
2 years ago
Solved

Power BI Dynamic Title for Matrix Table based on Customer Rank - Individual rank

I having trouble getting the title to show up on a matrix table.  I have created several matrix tables for monthly sales over fiscal years.  I created a Customer Rank Measure and also created another Filter Measure to in order to limit the matrix table to a specifically ranked customer.  So a customer ranked number 1 would be one table, customer ranked number 2 on a separate table and so on.  The entire sheet has filters on it, so this whole thing works dynamically, so when I choose a state, then the number 1 customer will change to whatever is the number 1 customer in that state.

 

The issue is that I cannot seem to get the actual name of the customer to show up on the table.  I understand that I can create a measure and add it to the the conditional formatting location as a function, but I cannot seem to figure how to write the measure to pull that single customer name.

 

Measures that I have created

Customer Rank = RANKX(ALL(Account[National Account Franchise Name]),[Sales Cases], ,DESC)
 
 
Number 1 Customer = CALCULATE([Sales Cases],
FILTER(VALUES(Account[National Account Franchise Name]),
IF(RANKX(ALL(Account[National Account Franchise Name]), [Sales Cases], ,DESC)=1, [Sales Cases], BLANK())))
 
The above measures have gotten me to create the following table
 

Need to create I guess a Dynamic Title Measure, but know idea how to specifically pull a single customer name, but also be able to specifically get the customer ranked differently.

Below is an example of the customers, sales, and rankings.  So i need a meaure that would specifically pull "independent" as number 1.... and/or a measure that would "costco" and so on.

 

 

Thanks in advance!

 

 

  • superlangford , you can use Index function for that

     

    You need a measure like

    maxx(index(1, allselected(Table[Customer]), orderBy([Your Measure], desc)) [Customer])

     

    Ideally, if customer is context, Rank =1 should filter it

     

    Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

2 Replies

  • superlangford , you can use Index function for that

     

    You need a measure like

    maxx(index(1, allselected(Table[Customer]), orderBy([Your Measure], desc)) [Customer])

     

    Ideally, if customer is context, Rank =1 should filter it

     

    Continue to explore Power BI Index function: Top/Bottom Performer by name and value- https://youtu.be/HPhzzCwe10U

  • Thank you.  While your formula did not exactly solve the issue, I was able to work out a solution from the Index Function.

    One follow up, is that with the index function and selecting 1, we are hardcoding that into the formula.  I thus would need to create multiple in order to get Rank = 5, Rank =7, etc.  Sounds trivial, but it is an interesting challenge.