Forum Discussion

ibuhary's avatar
ibuhary
Icon for Helper I rankHelper I
7 years ago
Solved

Remove RANKX from Total Row and Hide from Non Related Filters

Hello everyone,

 

I have the few Created Measures in my Database:

 

- StoreRank = RANKX(ALL(Table[Store Code],Table[Store Name]),CALCULATE(SUM(Table[Sales Volume])),,DESC)

 

 - ProductRank = RANKX(ALL(Table[Product Code],Table[Product Name]),CALCULATE(SUM(Table[Sales Volume])),,DESC)

 

I need to know:

 

1. When I Built a Table by Columns Store Code, Store Name, Sales Volume and Store Rank, how not to show any Ranks in the Total at the bottom, at the moment I get a number - 1 rank for the top store and also the total at the bottom also is given a Number - 1 Rank I want to make it blank in there.

 

2. If I populated a Table with Product details and chose to put in one column the Store Rank, it shows Rank - 1 for all the products, I want to make it Blank as well because I didnt chose any columns related to Store in this table.

  • ibuhary

     

    For your first question, you can use HASONEFILTER to detect totals/subtotals

     

    Something like this

     

    - StoreRank =
    IF (
        HASONEFILTER ( Table[Store Code] ),
        RANKX (
            ALL ( Table[Store Code], Table[Store Name] ),
            CALCULATE ( SUM ( Table[Sales Volume] ) ),
            ,
            DESC
        )
    )
    

3 Replies

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Icon for Community Champion rankCommunity Champion

      ibuhary

       

      For your first question, you can use HASONEFILTER to detect totals/subtotals

       

      Something like this

       

      - StoreRank =
      IF (
          HASONEFILTER ( Table[Store Code] ),
          RANKX (
              ALL ( Table[Store Code], Table[Store Name] ),
              CALCULATE ( SUM ( Table[Sales Volume] ) ),
              ,
              DESC
          )
      )
      
      • ibuhary's avatar
        ibuhary
        Icon for Helper I rankHelper I

        can you help me on another issue please ?