Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
Expand all | Collapse all

Global Rank based on Visual and Page Filters but should not affected by slicer selection

  • 1.  Global Rank based on Visual and Page Filters but should not affected by slicer selection

    Posted Dec 16, 2022 03:14 AM
    Edited by Mahalakshmi Baskaran Dec 16, 2022 03:15 AM
      |   view attached
    I have a major headache now. My client wants Global Ranking and it should not be changed if he make any slicer selection. Below are the issues which I'm facing
    1. I used ALL Function - Got Static Ranking but the problem is my table data is a filtered data based on visual and page filters. But this filters are omitted by All Function and it ranks all the data
    2. I used ALLSELECTED Function - It Ranks based on my filtered data but the Ranking is not static and it is dynamic
    3. My issue is My Rank should be based on my filtered data in my data table and also it should be static and should not be affected by slicer.

    Below is my code to generate Rank. This produces Dynamic Ranking but I want static Ranking. kindly dont suggest ALL function as I mentioned i need to Rank based on filtered data in my report.

    Rank = 
    Var summry=
    SUMMARIZE(**ALLSELECTED**('Employee'),
            'Employee'[Employee Name],
                    "TotalScore",[Total Score],
                    "WinRate",[Win Rate],
                    "CustomerCount",[Customer Count])
    var tmp=                       ------- Here I'm doing Tie Breaker based on Win Rate & CustomerCount
    ADDCOLUMNS(summry,"RNK",       
    RANKX(summry,
        RANKX(summry, 
            RANKX (summry, [TotalScore])
                + DIVIDE( 
                    RANKX(summry, [WinRate],,ASC), 
                    (COUNTROWS(summry) + 1)
                )
        , , DESC) +
        + DIVIDE( 
                RANKX(summry,[CustomerCount],,ASC), 
                (COUNTROWS(summry) + 1)
                )
        , , ASC)
    )
    var rnk = MAXX(FILTER(tmp,'Employee'[Employee Name]=SELECTEDVALUE('Employee'[Employee Name])),[RNK])
    return rnk
    

    In the image, you can see it missing many ranks if I use ALL function as it Ranking all data and not filtered data.

    Please provide a solution for this




  • 2.  RE: Global Rank based on Visual and Page Filters but should not affected by slicer selection

    Posted Dec 16, 2022 09:04 PM
    Veja se removendo a interação ajuda.



    ------------------------------
    Vilmar Santos
    ------------------------------