Forum Discussion

Ramachandran's avatar
Ramachandran
Icon for Helper III rankHelper III
5 years ago
Solved

RANKX Ignore blank and zero

Hi, I'm trying to get without gap of Rank values (1,2,3,4, etc) but I could not get solution for this. someone help me on this. For your reference, CustomerLevelRank = IF ( NOT ( SELECTEDV...
Interesting Links
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    So you don't have blank customer...

    CustomerLevelRank =
VAR T1 = ADDCOLUMNS(ALLSELECTED(Customer[CustName]),"SALES",CALCULATE(SUM( FactTable[SalesAmt])))
RETURN
IF (
NOT ( SELECTEDVALUE ( FactTable[SalesAmt]) = BLANK () ),
RANKX ( FILTER ( T1,[SALES]<>BLANK()),[SALES],
CALCULATE ( SUM ( FactTable[SalesAmt])),
,
DESC,
DENSE
)
)
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Ramachandran 

 

Can you provide some sample data? Is there any blank CustName or CustId in your fact table? Change your sum a little bit...assume there is blank custname in fact table

 

CALCULATE ( SUM ( FactTable[SalesAmt]),FactTable [CustName]<>BLANK())

 

  • Ramachandran's avatar
    Ramachandran
    Icon for Helper III rankHelper III
    5 years ago

    Thanks for your comments. I tried but not working as expected.

    The data is look like

    thanks,

    Ram

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      So you don't have blank customer...

      CustomerLevelRank =
VAR T1 = ADDCOLUMNS(ALLSELECTED(Customer[CustName]),"SALES",CALCULATE(SUM( FactTable[SalesAmt])))
RETURN
IF (
NOT ( SELECTEDVALUE ( FactTable[SalesAmt]) = BLANK () ),
RANKX ( FILTER ( T1,[SALES]<>BLANK()),[SALES],
CALCULATE ( SUM ( FactTable[SalesAmt])),
,
DESC,
DENSE
)
)
      • Ramachandran's avatar
        Ramachandran
        Icon for Helper III rankHelper III
        5 years ago

        Thank you very much! It's working as expected.