Forum Discussion

tammyl's avatar
tammyl
Regular Visitor
2 years ago
Solved

Create Ranking to dynamically update based on filters

Hi, I'm looking to create ranking measure to dynamically rank when certain filters are applied:   In the below table, I want to group by ID, and rank based on sorting the ind to ascending and offer...
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi again tammyl 

    Sure thing. If we put calculation groups aside for the moment, I would recommend creating measures for each of the components of the ratio, then creating a measure that divides them.

    See Version 3 page of attached PBIX.

     

    Here are the measures I created:

    # Customers Accepted Rank 1 = 
VAR RankingTable =
    CALCULATETABLE (
        SUMMARIZE (
            Offers,
            Offers[ind],
            Offers[offer_dt],
            Offers[ID],
            Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result
        ),
        ALLSELECTED ( ), -- Rank within overall filter context of visual
        Offers[Offer_Action] = "Accepted" -- Filter "Accepted" before ranking
    )
VAR FilterRank1 =
    INDEX (
        1,
        RankingTable,
        ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),
        DEFAULT,
        PARTITIONBY ( Offers[ID] )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Offers[ID] ),
        KEEPFILTERS ( FilterRank1 )
    )
RETURN
    Result
    # Customers Delivered = 
CALCULATE (
    DISTINCTCOUNT ( Offers[ID] ),
    KEEPFILTERS ( Offers[Offer_Action] = "Delivered" )
)
    Accepted Rank 1 / Delivered Ratio = 
DIVIDE (
    [# Customers Accepted Rank 1],
    [# Customers Delivered]
)

     

    I'm hoping I captured your requirements correctly, otherwise the logic might need to be adjusted.

     

    Side note: Calculation Groups can now be authored in Power BI Desktop, in case you would like to look into that later.

     

    Regards

OwenAuger's avatar
OwenAuger
Super User
2 years ago

Hi again tammyl 

Sure thing. If we put calculation groups aside for the moment, I would recommend creating measures for each of the components of the ratio, then creating a measure that divides them.

See Version 3 page of attached PBIX.

 

Here are the measures I created:

# Customers Accepted Rank 1 = 
VAR RankingTable =
    CALCULATETABLE (
        SUMMARIZE (
            Offers,
            Offers[ind],
            Offers[offer_dt],
            Offers[ID],
            Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result
        ),
        ALLSELECTED ( ), -- Rank within overall filter context of visual
        Offers[Offer_Action] = "Accepted" -- Filter "Accepted" before ranking
    )
VAR FilterRank1 =
    INDEX (
        1,
        RankingTable,
        ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),
        DEFAULT,
        PARTITIONBY ( Offers[ID] )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Offers[ID] ),
        KEEPFILTERS ( FilterRank1 )
    )
RETURN
    Result
# Customers Delivered = 
CALCULATE (
    DISTINCTCOUNT ( Offers[ID] ),
    KEEPFILTERS ( Offers[Offer_Action] = "Delivered" )
)
Accepted Rank 1 / Delivered Ratio = 
DIVIDE (
    [# Customers Accepted Rank 1],
    [# Customers Delivered]
)

 

I'm hoping I captured your requirements correctly, otherwise the logic might need to be adjusted.

 

Side note: Calculation Groups can now be authored in Power BI Desktop, in case you would like to look into that later.

 

Regards

tammyl's avatar
tammyl
Regular Visitor
2 years ago

Hi Owen,

 

I realized the measure doesn't really work as intended. I want the calculation to filter on Offer Action after ranking. For the filtered date, it should be 1 accepted, 1 deferred and 1 rejected. Could you advise how to filter offer action after the ranking? 

 

 

# Customers Rejected =
VAR RankingTable =
    CALCULATETABLE (
        SUMMARIZE (
            Offers,
            Offers[ind],
            Offers[offer_dt],
            Offers[ID],
            Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result
        ),
        ALLSELECTED ( ) , -- Rank within overall filter context of visual
        Offers[Offer_Action] = "Rejected" -- Filter "Rejected" before ranking
    )
VAR FilterRank1 =
    INDEX (
        1,
        RankingTable,
        ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),
        DEFAULT,
        PARTITIONBY ( Offers[ID] )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Offers[ID] ),
        KEEPFILTERS ( FilterRank1 )
    )
RETURN
    Result
 
 
# Customers Deferred Rank 1 =
VAR RankingTable =
    CALCULATETABLE (
        SUMMARIZE (
            Offers,
            Offers[ind],
            Offers[offer_dt],
            Offers[ID],
            Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result
        ),
        ALLSELECTED ( ), -- Rank within overall filter context of visual
        Offers[Offer_Action] = "Deferred" -- Filter "Deferred" before ranking
    )
VAR FilterRank1 =
    INDEX (
        1,
        RankingTable,
        ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),
        DEFAULT,
        PARTITIONBY ( Offers[ID] )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Offers[ID] ),
        KEEPFILTERS ( FilterRank1 )
    )
RETURN
    Result
  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi again Tammy,

    I think this structure is what you want:

    # Customers Rejected = 
VAR RankingTable =
    CALCULATETABLE (
        SUMMARIZE (
            Offers,
            Offers[ind],
            Offers[offer_dt],
            Offers[ID],
            Offers[Offer_Action] -- include this column to ensure it applies as a filter in Result
        ),
        ALLSELECTED ( )  -- Rank within overall filter context of visual
    )
VAR FilterRank1 =
    INDEX (
        1,
        RankingTable,
        ORDERBY ( Offers[ind], ASC, Offers[offer_dt], DESC ),
        DEFAULT,
        PARTITIONBY ( Offers[ID] )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( Offers[ID] ),
        KEEPFILTERS ( FilterRank1 ),
        KEEPFILTERS ( Offers[Offer_Action] = "Rejected" )
    )
RETURN
    Result

    The Offer_Action filter is shifted to the last step, and applied alongside the Rank = 1 filter.

     

    Does this work for you?