Custom ranking (preassigned values)

[Anonymous]

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 field	Text field	Text field	Text field	numeric field
Customer	Product	Month	Financial Year	Values USD	Expected Result	Condition
Microsoft	Mobile	July' 2015	2015-16	4,000	1	<- as per formula based on field "Financial Year" & sum of "Values USD"
FaceBook	Mobile	Aug' 2015	2015-16	3,000	2	<- as per formula based on field "Financial Year" & sum of "Values USD"
Apple	Mobile	July' 2015	2015-16	2,000	3	<- as per formula based on field "Financial Year" & sum of "Values USD"
General Motors	Mobile	July' 2015	2015-16	1,000	4	<- as per formula based on field "Financial Year" & sum of "Values USD"
Multiple Customers	Mobile	Aug' 2015	2015-16	5,000	301	<- conditioned to assign Rank no 301 or any such custom Rank number defined by me
Cargill	Mobile	July' 2015	2015-16	100		<- as per formula based on field "Financial Year" & sum of "Values USD"
Walmart	Mobile	July' 2015	2015-16	50		<- as per formula based on field "Financial Year" & sum of "Values USD"
Microsoft	Mobile	June' 2016	2016-17	4,130	2	<- as per formula based on field "Financial Year" & sum of "Values USD"
FaceBook	Mobile	May' 2016	2016-17	6,098	1	<- as per formula based on field "Financial Year" & sum of "Values USD"
Apple	Mobile	Mar' 2017	2016-17	2,065	3	<- as per formula based on field "Financial Year" & sum of "Values USD"
General Motors	Mobile	April' 2016	2016-17	1,033	4	<- as per formula based on field "Financial Year" & sum of "Values USD"
Multiple Customers	Mobile	September' 2017	2016-17	5,163	301	<- conditioned to assign Rank no 301 or any such custom Rank number defined by me
Cargill	Mobile	October' 2016	2016-17	103		<- as per formula based on field "Financial Year" & sum of "Values USD"
Walmart	Mobile	September' 2016	2016-17	52		<- as per formula based on field "Financial Year" & sum of "Values USD"
Microsoft	Mobile		2017-18	4,264	2	<- as per formula based on field "Financial Year" & sum of "Values USD"
FaceBook	Mobile		2017-18	6,296	1	<- as per formula based on field "Financial Year" & sum of "Values USD"
Apple	Mobile		2017-18	2,132	3	<- as per formula based on field "Financial Year" & sum of "Values USD"
General Motors	Mobile		2017-18	1,066	4	<- as per formula based on field "Financial Year" & sum of "Values USD"
Multiple Customers	Mobile		2017-18	5,330	301	<- conditioned to assign Rank no 301 or any such custom Rank number defined by me
Cargill	Mobile		2017-18	107		<- as per formula based on field "Financial Year" & sum of "Values USD"
Walmart	Mobile		2017-18	53		<- as per formula based on field "Financial Year" & sum of "Values USD"

[Zubair_Muhammad]

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 · Accepted solution]

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]

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]

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

[Anonymous]

Hi @v-huizhn-msft , Yes, Rank is required for those blank cells . I kept it blank in the "expected result" field but the adjacent field named "condition" states " as per formula based on field "Financial Year" & sum of "Values USD" ".

[v-huizhn-msft]

Hi @Anonymous,

Ok, got it. Please try the solution @Zubair_Muhammad posted, please mark the right reply as answer if your issue has been resolved, and respond to us if you still have other problems.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft, yes I got to check that offered solution given by @Zubair_MuhammadZubair Mohammad. I thought if in case you want to contribute from your side in a different way 🙂

[v-huizhn-msft]

Hi @Anonymous,

Have you tried the solution @Zubair_Muhammad posted? Have you resolved your issue? If you have, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

[Anonymous]

Hi @v-huizhn-msft, the solution worked by Mr. Zubair for summarised recrods in a table. I am now looking for a cmore lengthy data with multiple values in the same financial year.

I will post the PBX file & tag you as well in the question.

Thanks,

Chandan