Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
johnmelbourne
Helper V
Helper V

Use the value of a customer rank in a card visual

I have a table visual with the following values .... and I want to place the rank of Customer A in a card. That is, the number 2. 

However, obviously when I filter for the customer, it only has one row and therefore ranks 1. 

I want to return the rank of customer A in the content of all the customers. 

5.png

PBIX link: 

https://drive.google.com/file/d/1WuEalnD65nIrCjJJNV95TAnWp6hN7QdW/view?usp=share_link

 

Thanks for any ideas.

 

 

2 ACCEPTED SOLUTIONS
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

I think, in this case, you can try to use ALL, instead of using ALLSELECTED.

 

Jihwan_Kim_0-1671692920750.png

 

 

Rank solution =
IF (
    HASONEVALUE ( Customers[Customer] ),
    IF (
        NOT ( ISBLANK ( [Total sales] ) ),
        RANKX (
            FILTER ( ALL ( 'Customers'[Customer] ), NOT ( ISBLANK ( [Total sales] ) ) ),
            [Total sales]
        )
    ),
    "select one customer"
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

johnmelbourne
Helper V
Helper V

SOLUTION:
 
Rank of customer =
var _tVisibleCustomers =
    CALCULATETABLE(
        FILTER (
            ALLSELECTED ( 'Customers'[Customer] ),
            NOT ( ISBLANK ( [Total sales] ) )
        ))
VAR _sResult = CALCULATE( RANKX( _tVisibleCustomers, [Total sales],,DESC,Dense), Customers[Customer] = "A")                  
Return
_sResult  

View solution in original post

3 REPLIES 3
johnmelbourne
Helper V
Helper V

SOLUTION:
 
Rank of customer =
var _tVisibleCustomers =
    CALCULATETABLE(
        FILTER (
            ALLSELECTED ( 'Customers'[Customer] ),
            NOT ( ISBLANK ( [Total sales] ) )
        ))
VAR _sResult = CALCULATE( RANKX( _tVisibleCustomers, [Total sales],,DESC,Dense), Customers[Customer] = "A")                  
Return
_sResult  
Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

I think, in this case, you can try to use ALL, instead of using ALLSELECTED.

 

Jihwan_Kim_0-1671692920750.png

 

 

Rank solution =
IF (
    HASONEVALUE ( Customers[Customer] ),
    IF (
        NOT ( ISBLANK ( [Total sales] ) ),
        RANKX (
            FILTER ( ALL ( 'Customers'[Customer] ), NOT ( ISBLANK ( [Total sales] ) ) ),
            [Total sales]
        )
    ),
    "select one customer"
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Brilliant @Jihwan_Kim !  I didn't think of ALL. 
Thank you @Jihwan_Kim.  I had a solution, but wanted exactly what you gave me. You took it that extra step that I needed and made it dynamic. I like the HASONEVALUE error message solution as well. 
Many thanks.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.