Rank based on total sub total count.

[Sandeep13]

Hello All,

Want to rank on my matrix based on total of CLIENT_SUPPLIER  but first do sum of Product which is part of matrix table and give rank like below

I am expecting output like below

Note  Total_ProductWise is sum of Total_Count_Measure for each Product.

CLIENT_SUPPLIER	Product	Region	SM Name	Total_Count_Measure	Total_ProductWise	Rank
Acko General Insurance	CCM_SMARTMATCH_ACKO	Strat a/c	Yogesh Rote	4222619	6753518	1
Acko General Insurance	ONLINE ACCOUNT REVIEW	Strat a/c	Yogesh Rote	2530899	6753518	1
Aditya Birla Finance ltd	CCM_SMARTMATCH_V3	West	Sheeba Nadar	1919624	3874186	2
Aditya Birla Finance ltd	CCM_V3	West	Sheeba Nadar	1681726	3874186	2
Aditya Birla Finance ltd	CIR_V3	West	Sheeba Nadar	272836	3874186	2

Below is sample dataset for your reference. 

CLIENT_SUPPLIER	Product	Region	SM Name	Total_Count_Measure
510 ARMY BASE WORKSHOP CREDIT CO-OPERATIVE PRIMARY BANK LTD	CIR	RemoteDesk	RemoteDesk	2
510 ARMY BASE WORKSHOP CREDIT CO-OPERATIVE PRIMARY BANK LTD	CIR ADVANCED	RemoteDesk	RemoteDesk	6
Abhinandan Urban Cooperative Bank Ltd	CIR ADVANCED	RemoteDesk	RemoteDesk	1
Abhyudaya Cooperative Bank Limited				2
Acko General Insurance	CCM_SMARTMATCH_ACKO	Strat a/c	Yogesh Rote	4222619
Acko General Insurance	ONLINE ACCOUNT REVIEW	Strat a/c	Yogesh Rote	2530899
Aditya Birla Finance ltd	CCM_SMARTMATCH_V3	West	Sheeba Nadar	1919624
Aditya Birla Finance ltd	CCM_V3	West	Sheeba Nadar	1681726
Aditya Birla Finance ltd	CIR_V3	West	Sheeba Nadar	272836
Aditya Birla Housing Finance Ltd	CIR	West	Sheeba Nadar	9817
Aegon Life Insurance	ONLINE ACCOUNT REVIEW	Strat a/c	Yogesh Rote	13810
AGRIWISE FINSERV LIMITED	CIR	RemoteDesk	RemoteDesk	34
AGRIWISE FINSERV LIMITED	COMMERCIAL CIR	RemoteDesk	RemoteDesk	207
AKD VINIMAY PRIVATE LIMITED				1
Akhand Anand Co-op. Bank Ltd	CIR	RemoteDesk	RemoteDesk	6
Akhand Anand Co-op. Bank Ltd	CIR ADVANCED	RemoteDesk	RemoteDesk	141
Ambit Finvest Pvt Ltd	COMMERCIAL CIR	West	Vimmi Upadhyaya	10
Ambit Finvest Pvt Ltd	COMMERCIAL_CIR_DIP1	West	Vimmi Upadhyaya	1
AMROS COMMERCIAL PRIVATE LIMITED	ONLINE_ACCOUNT_REVIEW_V3	West	Sourav Halder	8672
ANANDA COOPERATIVE BANK LTD	CIR	RemoteDesk	RemoteDesk	3
ANANDA COOPERATIVE BANK LTD	CIR ADVANCED	RemoteDesk	RemoteDesk	9
Andhra Pragathi Grameena Bank	CIR	RemoteDesk	RemoteDesk	219
Andhra Pragathi Grameena Bank	CIR ADVANCED	RemoteDesk	RemoteDesk	1061
Andhra Pragathi Grameena Bank	CIR_ADVANCED_V3	RemoteDesk	RemoteDesk	13
ANTWORKS P2P FINANCING PVT LTD	CCM_SMARTMATCH_V3	North & East	TBH - North	39251
ANTWORKS P2P FINANCING PVT LTD	ONLINE ACCOUNT REVIEW	North & East	TBH - North	246
ANTWORKS P2P FINANCING PVT LTD	ONLINE_ACCOUNT_REVIEW_V3	North & East	TBH - North	25538
APAARSEVA FOUNDATION	CIR	RemoteDesk	RemoteDesk	304
APAARSEVA FOUNDATION	CIR ADVANCED	RemoteDesk	RemoteDesk	1
Aptus Value Hosuing Finance India ltd	CIR	South	Varadharajan R	64819
Aptus Value Hosuing Finance India ltd	CIR ADVANCED	South	Varadharajan R	3301
Arunachal Pradesh rural bank	CIR	RemoteDesk	RemoteDesk	2
Arunachal Pradesh rural bank	CIR ADVANCED	RemoteDesk	RemoteDesk	10
Arvind Sahakari Bank Ltd	CIR	RemoteDesk	RemoteDesk	2
ATD FINANCIAL SERVICES PVT LTD	CIR	RemoteDesk	RemoteDesk	1221
AU Small Finance Bank	CIR	North & East	Saurav Sahi	1
AU Small Finance Bank	CIR ADVANCED	North & East	Saurav Sahi	18
AU Small Finance Bank	COMMERCIAL CIR	North & East	Saurav Sahi	2
AU Small Finance Bank	ONLINE ACCOUNT REVIEW	North & East	Saurav Sahi	1314216
Australia and New Zealand Banking Group Limited	COMMERCIAL CIR	Strat a/c	Devicharan Yadav	2
Australia and New Zealand Banking Group Limited	COMMERCIAL_CIR_DIP1	Strat a/c	Devicharan Yadav	3
Avanse Financial Services Pvt. Ltd.	CIR	West	Sheeba Nadar	12130
Avanse Financial Services Pvt. Ltd.	CIR_V3	West	Sheeba Nadar	19173
Axis Bank	CCM_SMARTMATCH	Strat a/c	Ashu Anand	5268
Axis Bank	CIR	Strat a/c	Ashu Anand	6578554
Axis Bank	ONLINE ACCOUNT REVIEW	Strat a/c	Ashu Anand	4196
Aye Finance Private Limited	CIR	North & East	Saurav Sahi	14
Bajaj Finance Limited	CIR	Strat a/c	Rahul Dubey	310314
Bajaj Finserv	CCM_SMARTMATCH_V3	Strat a/c	Rahul Dubey	26
Bajaj Finserv	ONLINE_ACCOUNT_REVIEW_V3	Strat a/c	Rahul Dubey	20
Banas Finance Limited	CIR ADVANCED	RemoteDesk	RemoteDesk	1
Bank Of Baroda	CIR	Strat a/c	Ashu Anand	144
Bank Of Baroda	CIR ADVANCED	Strat a/c	Ashu Anand	424
Bank Of Baroda	COMMERCIAL CIR	Strat a/c	Ashu Anand	395
Bank Of Baroda	COMMERCIAL_CIR_DIP1	Strat a/c	Ashu Anand	5

Please let me know if you have any query

@power @ExpertBM  @Experteam  @PBICommunity  @leo-community   @Anonymous 

[Sandeep13]

Thanks Its works fine😊

[Anonymous · Accepted solution]

Hi @Sandeep13 ,

Please create 2 measures:

Total_ProductWise = 
    VAR CurrentClientSupplier = MAX('Table'[CLIENT_SUPPLIER])
    RETURN
        CALCULATE(
            SUM('Table'[Total_Count_Measure]),
            FILTER(
                ALL('Table'),
                'Table'[CLIENT_SUPPLIER] = CurrentClientSupplier
            )
        )

Rank = 
    VAR CurrentClientSupplier = MAX('Table'[CLIENT_SUPPLIER])
    VAR CurrentTotalProductWise = [Total_ProductWise]
    RETURN
        CALCULATE(
            RANKX(
                ALL('Table'[CLIENT_SUPPLIER]),
                [Total_ProductWise],
                ,
                DESC,
                Dense
            ),
            ALLEXCEPT('Table', 'Table'[CLIENT_SUPPLIER]),
            'Table'[CLIENT_SUPPLIER] = CurrentClientSupplier
        )

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum