Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Custom ranking (preassigned values)

  Hi Power BI Guru's, I am not a programmer & new to Pwer BI. I seek your help in a custom solution to ranking my data under "Matrix" visualization which I am building for Top 50/100 or 300 custom...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi Anonymous

     

    If you are looking for a MEASURE, you can use this formula

     

    RANK_Measure =
IF (
    SELECTEDVALUE ( TableName[Customer] ) = "Multiple Customers",
    301,
    RANKX (
        FILTER (
            ALL ( TableName ),
            TableName[Financial Year] = SELECTEDVALUE ( TableName[Financial Year] )
                && TableName[Customer] <> "Multiple Customers"
        ),
        CALCULATE ( SELECTEDVALUE ( TableName[Values USD] ) )
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

HI Anonymous

 

Please see the attached file here

Hope it helps

 

I just added a calculated column as follows

 

RANK =
IF (
    TableName[Customer] = "Multiple Customers",
    301,
    RANKX (
        FILTER (
            ALL ( TableName ),
            TableName[Financial Year] = EARLIER ( TableName[Financial Year] )
                && TableName[Customer] <> "Multiple Customers"
        ),
        TableName[Values USD]
    )
)
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    If you are looking for a MEASURE, you can use this formula

     

    RANK_Measure =
IF (
    SELECTEDVALUE ( TableName[Customer] ) = "Multiple Customers",
    301,
    RANKX (
        FILTER (
            ALL ( TableName ),
            TableName[Financial Year] = SELECTEDVALUE ( TableName[Financial Year] )
                && TableName[Customer] <> "Multiple Customers"
        ),
        CALCULATE ( SELECTEDVALUE ( TableName[Values USD] ) )
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Zubair_Muhammad, Thanks for this help. I will check it over this weekend, since PBI is new to me & hence I am slow in using it, I will take leisure time to learn & absorb. Will reply you on Sunday. Thanks Again !!

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

        In your expected result, there are some blank row, why they are blank, you only want to display the rank number which is less than 4 in your sample table?

        Best Regards,
        Angelia