Forum Discussion

m_wex's avatar
m_wex
Helper I
5 years ago

Ranking Output

Trying, unsuccessfully, to display a customer ranking list using the following:

 

Customer Ranking  =
IF(NOT(ISBLANK([Total Sales])), if([Total Sales]>0.01,RANKX(ALLSELECTED(Customers[Customer Name]),[Total Sales], , desc)))
 
The if statements are to weed out customers with no sales or those with sales under $0.01. The formula seems to work fine. But the output includes total sales of those customer who did not receive a rank. The desired result is to only display invoiced sales of customers receiving a rank.
 
Any help would be greatly appreciated.
 
Thanks,
m_wex
 
ranking

2 Replies

  • m_wex's avatar
    m_wex
    Helper I
    5 years ago

    Solved the issue by putting a filter on the field so that no blanks are displayed, But how do you prevent a rank on the total line? The table shows all the ranked customers and provides total sales. But it also shows a 1 in the ranking column. Is the function hasonevalue the one to use?

    • AntrikshSharma's avatar
      AntrikshSharma
      Community Champion
      5 years ago

      m_wex  You can use ISINSCOPE to check the if the Customers[Customer Name] is being used for grouping in the visual. IF ( ISINSCOPE  ( Customers[Customer Name] ), [Your Measure] )