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

  • tammyl's avatar
    tammyl
    Regular Visitor
    2 years ago

    Thank so much for your help!

  • 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?