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

tammyl's avatar
tammyl
Regular Visitor
2 years ago

Hi OwenAuger ,

 

Thanks so much for this. I don't have tabular editor but i was able to leverage this measure you provided and I was able to create the charts. I want to create additional calculations on this measure. How can I adjust this or use this measure to calculate a percentage. For example, I want to calculate the total # of unique customers who Accepted (based on the ranking=1 with date filter AND offer_action ='Accepted') / total # of customers who got an offer delivered (distinct count of ID and offer_action ='Delivered' - this does not need to leverage the ranking)

 

    CALCULATIONITEM "Rank = 1 (filter on date)" = 
        VAR RankingTable =
            CALCULATETABLE (
                SUMMARIZE (
                    Offers,
                    Offers[ind],
                    Offers[offer_dt],
                    Offers[ID]
                ),
                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 (
                SELECTEDMEASURE ( ),
                KEEPFILTERS ( FilterRank1 )
            )
        RETURN
            Result

 

Thanks,

 

Tammy

 

 

 

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