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 customers from my table, such that wierd names like "Multiple Customers" or some bankrupt customers records are kept out of the displayed Matrix BUT, they continue to contribute to total value.

The 2nd row of the table in blue color is my table header.

1) First, I should be able to assign a custom rank number to the specific values in the field "Customer" (say "Multiple Customer" or any bankrupt customer.

2) then, the rest records are ranked based on the field "Financial Year" and sum of field "Values USD". No problem if the custom rank numer I chose in point 1 above, is also assinged to other records by the PBI formula.

The result I expect is in the field "Expected Resut".

Please help with this unusual help sought :-)

Thanks,

Chandan.

Text fieldText fieldText fieldText fieldnumeric field  
CustomerProductMonthFinancial YearValues USDExpected ResultCondition
MicrosoftMobileJuly' 20152015-16                4,0001<- as per formula based on field "Financial Year" & sum of "Values USD"
FaceBookMobileAug' 20152015-16                3,0002<- as per formula based on field "Financial Year" & sum of "Values USD"
AppleMobileJuly' 20152015-16                2,0003<- as per formula based on field "Financial Year" & sum of "Values USD"
General MotorsMobileJuly' 20152015-16                1,0004<- as per formula based on field "Financial Year" & sum of "Values USD"
Multiple CustomersMobileAug' 20152015-16                5,000301<- conditioned to assign Rank no 301 or any such custom Rank number defined by me
CargillMobileJuly' 20152015-16                   100 <- as per formula based on field "Financial Year" & sum of "Values USD"
WalmartMobileJuly' 20152015-16                      50 <- as per formula based on field "Financial Year" & sum of "Values USD"
MicrosoftMobileJune' 20162016-17                4,1302<- as per formula based on field "Financial Year" & sum of "Values USD"
FaceBookMobileMay' 20162016-17                6,0981<- as per formula based on field "Financial Year" & sum of "Values USD"
AppleMobileMar' 20172016-17                2,0653<- as per formula based on field "Financial Year" & sum of "Values USD"
General MotorsMobileApril' 20162016-17                1,0334<- as per formula based on field "Financial Year" & sum of "Values USD"
Multiple CustomersMobileSeptember' 20172016-17                5,163301<- conditioned to assign Rank no 301 or any such custom Rank number defined by me
CargillMobileOctober' 20162016-17                   103 <- as per formula based on field "Financial Year" & sum of "Values USD"
WalmartMobileSeptember' 20162016-17                      52 <- as per formula based on field "Financial Year" & sum of "Values USD"
MicrosoftMobile 2017-18                4,2642<- as per formula based on field "Financial Year" & sum of "Values USD"
FaceBookMobile 2017-18                6,2961<- as per formula based on field "Financial Year" & sum of "Values USD"
AppleMobile 2017-18                2,1323<- as per formula based on field "Financial Year" & sum of "Values USD"
General MotorsMobile 2017-18                1,0664<- as per formula based on field "Financial Year" & sum of "Values USD"
Multiple CustomersMobile 2017-18                5,330301<- conditioned to assign Rank no 301 or any such custom Rank number defined by me
CargillMobile 2017-18                   107 <- as per formula based on field "Financial Year" & sum of "Values USD"
WalmartMobile 2017-18                      53 <- as per formula based on field "Financial Year" & sum of "Values USD"
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] ) )
    )
)

9 Replies

  • 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 !!