Custom Ranking (preassigned Rank)

[Anonymous]

Hello Power BI Guru's,

I am not a programmer & also new to Power BI.

I seek a solution to my report with "Matrix" visualisation.

The table below with blue color text (2nd row) is my table header. Rest rows are fictitious values I built to seek help from you' ll.

Q - I am looking for a Customised Ranking Solution -

  a) First of all, I can assign a predefined rank to values like "Multiple Customers" and any other few more values (say Bankrupt companies) that I find in field "Customer".

That predefined values (Customer Rank) can be 51, 101, 301, etc., in short, I should be able to define that and it is applicable to entire table wherever such identified value appears.

   b) & the rest cases are Ranked based on field "Financial year" with highest value by sum of field "Values USD" in that financial year.

See the field "Expected Result" for what I expect as an answer.

The idea is that such names like "Multiple Customers" or any Bankrupt Companies dont appear in my Top 300 (or say Top 100 or 500) customers in "Matrix Visualisation".

I have no problem if that same "Custom Rank" number I defined, is also shared with any other customer assigned by the formula/(s).

Thank,

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 field "Values USD"
FaceBook	Mobile	Aug' 2015	2015-16	3,000	2	<- as per formula based on field "Financial Year" & Sum of field "Values USD"
Apple	Mobile	July' 2015	2015-16	2,000	3	<- as per formula based on field "Financial Year" & Sum of field "Values USD"
General Motors	Mobile	July' 2015	2015-16	1,000	4	<- as per formula based on field "Financial Year" & Sum of field "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 field "Values USD"
Walmart	Mobile	July' 2015	2015-16	50		<- as per formula based on field "Financial Year" & Sum of field "Values USD"
Microsoft	Mobile	June' 2016	2016-17	4,130	2	<- as per formula based on field "Financial Year" & Sum of field "Values USD"
FaceBook	Mobile	May' 2016	2016-17	6,098	1	<- as per formula based on field "Financial Year" & Sum of field "Values USD"
Apple	Mobile	Mar' 2017	2016-17	2,065	3	<- as per formula based on field "Financial Year" & Sum of field "Values USD"
General Motors	Mobile	April' 2016	2016-17	1,033	4	<- as per formula based on field "Financial Year" & Sum of field "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 field "Values USD"
Walmart	Mobile	September' 2016	2016-17	52		<- as per formula based on field "Financial Year" & Sum of field "Values USD"

[Ashish_Mathur]

Hi @Anonymous,

Why have you not assigned a rank to Cargill and Walmart in 2015-16?  Shouldn't they be ranked 6 and 5 respectively?  The same if for 2016-17 as well.  Please clarify.

[Anonymous]

Hi @Ashish_Mathur, Sorry was tied up into another task & could not reply to you.

This post got duplicated for unknown reason. Actually @Zubair_Muhammad gave me a solution which worked well in summarised records. However, when I apply it to lengthy records where customer names appear multiple time in the same financial year, then for each line there are seperate Rank assigned (looks like).

Please see attached PBX file in OneDrive - https://1drv.ms/u/s!ArDh_5URL_X_bJxn8Pm3cMwnVfo 

with new table "Ranking_Sample" & page name = "Chandan".

I opted to take MIN of Rank, however, that defeats the purpose of keeping single Rank for each customer in a financial year and I get ranks like 17 & 37 whereas there are just Five customers in my table.

Need help.

Thanks,

Chandan

[Ashish_Mathur]

Hi,

Looks like Zubair has answer your question.

[Zubair_Muhammad]

Hi @Anonymous

Link doesn't work

[Anonymous]

Hi @Zubair_Muhammad

I rechecked and its the same link "https://1drv.ms/u/s!ArDh_5URL_X_bJxn8Pm3cMwnVfo"

Can you please check again ?

[Zubair_Muhammad]

HI @Anonymous

Try this MEASURE

RANK Measure R =
IF (
    SELECTEDVALUE ( Ranking_Sample[Customer] ) = "Multiple Customers",
    301,
    IF (
        HASONEVALUE ( Ranking_Sample[Customer] ),
        RANKX (
            ALL ( Ranking_Sample[Customer] ),
            CALCULATE ( SUM ( Ranking_Sample[Amount_USD] ) ),
            ,
            desc,
            DENSE
        )
    )
)

[Zubair_Muhammad]

@Anonymous

[Anonymous]

Hi @Zubair_Muhammad,

Sorry was again stuck into some lengthy tasks & did not respond.

I was actually referrring to another table in my sample PBX file in OneDrive.

The name of table is "TableName" & has same customer appearing multiple times in the same financial year.

"https://1drv.ms/u/s!ArDh_5URL_X_bJxn8Pm3cMwnVfo"  Pleaser refer table name "TableName".

Within the same financialyear, no matter how many time a customer name appears, Only One single rank should get calculated in that financial year based on field "Value_USD" .

Thanks,

Chandan.

[v-sihou-msft]

@Anonymous

Based on your description, you want to get the RANK within each "Financial Year" group. Right? 

You can achieve it with either measure or calculated column: 

RankWithinGroup Measure =
RANKX (
    CALCULATETABLE (
        ALLSELECTED ( Table[Customer] ),
        VALUES ( Table[FinancialYear] )
    ),
    CALCULATE ( SUM ( Table[Values_USD] ) ),
    ,
    DESC,
    DENSE
)

RankWithinGroup Column =
RANKX (
    ALL ( Table[Customer] ),
    CALCULATE (
        SUM ( Table[Values_USD] ),
        ALLEXCEPT ( Table, Table4[FinancialYear], Table[Customer] )
    ),
    ,
    DESC,
    DENSE
)

Regards,

[Anonymous]

Hi @v-sihou-msft,

Both the measure & cloumn formula are not giving correct Rank. Measure gives me "1" as rank for all customer while column give me "84" as the rank for all csutomer.

I have uploaded the sample PBX file on OneDrive -  https://1drv.ms/u/s!ArDh_5URL_X_bJxn8Pm3cMwnVfo

Can you please provide a solution by applying measure and column in the attached PBX file for table named as "TableName" ?