Forum Discussion

Sandeep13's avatar
Sandeep13
Helper III
3 years ago
Solved

Rank based on total sub total count.

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_SUPPLIERProductRegionSM NameTotal_Count_MeasureTotal_ProductWiseRank
Acko General InsuranceCCM_SMARTMATCH_ACKOStrat a/cYogesh Rote422261967535181
Acko General InsuranceONLINE ACCOUNT REVIEWStrat a/cYogesh Rote253089967535181
Aditya Birla Finance ltdCCM_SMARTMATCH_V3WestSheeba Nadar191962438741862
Aditya Birla Finance ltdCCM_V3WestSheeba Nadar168172638741862
Aditya Birla Finance ltdCIR_V3WestSheeba Nadar27283638741862

 

Below is sample dataset for your reference. 

CLIENT_SUPPLIERProductRegionSM NameTotal_Count_Measure
510 ARMY BASE WORKSHOP CREDIT CO-OPERATIVE PRIMARY BANK LTDCIRRemoteDeskRemoteDesk2
510 ARMY BASE WORKSHOP CREDIT CO-OPERATIVE PRIMARY BANK LTDCIR ADVANCEDRemoteDeskRemoteDesk6
Abhinandan Urban Cooperative Bank LtdCIR ADVANCEDRemoteDeskRemoteDesk1
Abhyudaya Cooperative Bank Limited   2
Acko General InsuranceCCM_SMARTMATCH_ACKOStrat a/cYogesh Rote4222619
Acko General InsuranceONLINE ACCOUNT REVIEWStrat a/cYogesh Rote2530899
Aditya Birla Finance ltdCCM_SMARTMATCH_V3WestSheeba Nadar1919624
Aditya Birla Finance ltdCCM_V3WestSheeba Nadar1681726
Aditya Birla Finance ltdCIR_V3WestSheeba Nadar272836
Aditya Birla Housing Finance LtdCIRWestSheeba Nadar9817
Aegon Life InsuranceONLINE ACCOUNT REVIEWStrat a/cYogesh Rote13810
AGRIWISE FINSERV LIMITEDCIRRemoteDeskRemoteDesk34
AGRIWISE FINSERV LIMITEDCOMMERCIAL CIRRemoteDeskRemoteDesk207
AKD VINIMAY PRIVATE LIMITED   1
Akhand Anand Co-op. Bank LtdCIRRemoteDeskRemoteDesk6
Akhand Anand Co-op. Bank LtdCIR ADVANCEDRemoteDeskRemoteDesk141
Ambit Finvest Pvt LtdCOMMERCIAL CIRWestVimmi Upadhyaya10
Ambit Finvest Pvt LtdCOMMERCIAL_CIR_DIP1WestVimmi Upadhyaya1
AMROS COMMERCIAL PRIVATE LIMITEDONLINE_ACCOUNT_REVIEW_V3WestSourav Halder8672
ANANDA COOPERATIVE BANK LTDCIRRemoteDeskRemoteDesk3
ANANDA COOPERATIVE BANK LTDCIR ADVANCEDRemoteDeskRemoteDesk9
Andhra Pragathi Grameena BankCIRRemoteDeskRemoteDesk219
Andhra Pragathi Grameena BankCIR ADVANCEDRemoteDeskRemoteDesk1061
Andhra Pragathi Grameena BankCIR_ADVANCED_V3RemoteDeskRemoteDesk13
ANTWORKS P2P FINANCING PVT LTDCCM_SMARTMATCH_V3North & EastTBH - North39251
ANTWORKS P2P FINANCING PVT LTDONLINE ACCOUNT REVIEWNorth & EastTBH - North246
ANTWORKS P2P FINANCING PVT LTDONLINE_ACCOUNT_REVIEW_V3North & EastTBH - North25538
APAARSEVA FOUNDATIONCIRRemoteDeskRemoteDesk304
APAARSEVA FOUNDATIONCIR ADVANCEDRemoteDeskRemoteDesk1
Aptus Value Hosuing Finance India ltdCIRSouthVaradharajan R64819
Aptus Value Hosuing Finance India ltdCIR ADVANCEDSouthVaradharajan R3301
Arunachal Pradesh rural bankCIRRemoteDeskRemoteDesk2
Arunachal Pradesh rural bankCIR ADVANCEDRemoteDeskRemoteDesk10
Arvind Sahakari Bank LtdCIRRemoteDeskRemoteDesk2
ATD FINANCIAL SERVICES PVT LTDCIRRemoteDeskRemoteDesk1221
AU Small Finance BankCIRNorth & EastSaurav Sahi1
AU Small Finance BankCIR ADVANCEDNorth & EastSaurav Sahi18
AU Small Finance BankCOMMERCIAL CIRNorth & EastSaurav Sahi2
AU Small Finance BankONLINE ACCOUNT REVIEWNorth & EastSaurav Sahi1314216
Australia and New Zealand Banking Group LimitedCOMMERCIAL CIRStrat a/cDevicharan Yadav2
Australia and New Zealand Banking Group LimitedCOMMERCIAL_CIR_DIP1Strat a/cDevicharan Yadav3
Avanse Financial Services Pvt. Ltd.CIRWestSheeba Nadar12130
Avanse Financial Services Pvt. Ltd.CIR_V3WestSheeba Nadar19173
Axis BankCCM_SMARTMATCHStrat a/cAshu Anand5268
Axis BankCIRStrat a/cAshu Anand6578554
Axis BankONLINE ACCOUNT REVIEWStrat a/cAshu Anand4196
Aye Finance Private LimitedCIRNorth & EastSaurav Sahi14
Bajaj Finance LimitedCIRStrat a/cRahul Dubey310314
Bajaj FinservCCM_SMARTMATCH_V3Strat a/cRahul Dubey26
Bajaj FinservONLINE_ACCOUNT_REVIEW_V3Strat a/cRahul Dubey20
Banas Finance LimitedCIR ADVANCEDRemoteDeskRemoteDesk1
Bank Of BarodaCIRStrat a/cAshu Anand144
Bank Of BarodaCIR ADVANCEDStrat a/cAshu Anand424
Bank Of BarodaCOMMERCIAL CIRStrat a/cAshu Anand395
Bank Of BarodaCOMMERCIAL_CIR_DIP1Strat a/cAshu Anand5

 

Please let me know if you have any query

power ExpertBM  Experteam  PBICommunity  leo-community   Anonymous 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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