Forum Discussion

amotto11's avatar
amotto11
Helper II
8 years ago

Counting a Rank with Conditions

I have, what i see to be, a very complex problem that i can't seem to even get started with. In my simple example i have 2 tables. One that contains insurance quotes. The table is broken into policyl...
Need Help
OwenAuger's avatar
OwenAuger
Super User
8 years ago

amotto11

Here is an attempt at it:

(pbix uploaded for reference)

 

Note: I have named the tables Quotes and Companies.

 

Quote Count = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            >= 2
    )
RETURN
    COUNTROWS ( ValidPolicies )
# Rank 1 = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            >= 2
    )
VAR ValidPoliciesMinPremiums =
    GENERATE (
        ValidPolicies,
        CALCULATETABLE (
            FIRSTNONBLANK ( Quotes[Premium], 0 ),
            ALLSELECTED ( Companies )
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Quotes[PolicyLinkID] ), ValidPoliciesMinPremiums )

In both measures, the ValidPolicies variable stores those PolicyLinkID values that have 2+ Companies.

In the # Rank 1 measure, the ValidPoliciesMinPremiums variable contains a two-column table consisting of ValidPolicies paired with the minimum Premium for each policy. This is then used as a filter in order to count the Policies for which the currently filtered Company has the minimum Premium.

 

I have used DISTINCTCOUNT ( Quotes[PolicyLinkID] ) in # Rank 1, just as a precaution in case their were duplicate quotes for the same company on one policy.

 

One question I had was: In your sample outputs, you only showed Quote Count for Companies with nonblank # Rank 1 values. However the Quote Count measure above shows values for all companies that have quotes for the filtered policies, regardless of their # Rank 1 value.

 

Regards,

Owen

  • amotto11's avatar
    amotto11
    Helper II
    8 years ago

    Owen,

     

    I think your solution works!!! I had to change the distinctcount to count because Directquery was throwing an error saying something about not being able to use direct query with over x number of rows. I feel confident however there are not duplicate quotes for the same company because my scrubbing of the data in SQL eliminates this. I am going to test some more tomorrow and monday to make sure it is working as intended, but at first glance it appears to be working. With regards to your other question, i have adjusted my original question as my first results table was incorrect as you point out. Thank you again for your help. I will report back weather it works or not when i have tested it to a greater extent.

  • amotto11's avatar
    amotto11
    Helper II
    8 years ago

    OwenAuger

     

    It looks like your solution works! I have two to three more measures that i would like to show in this document. Would you mind helping me calculate these:

     

    # Rank Top 2 - It should be the count of the number of times that a company is ranked #1 or #2

    Avgerage $ in First - When a company is in first i want to calculate the number of dollars they are winning by and take an average for every quote.

     

    If it can be done easily my final measure would be Average $ in First by Premium - This measure would divide the average dollars in first by the premium and take an average percentage for each company.

     

    Thank you again for your help!

    • OwenAuger's avatar
      OwenAuger
      Super User
      8 years ago

      Hi again amotto11

       

      I have updated the pbix I linked to earlier with your first two additional measures.

      I won't say these are optimal, but they do the trick as far as I can see. You will need to verify & test with DirectQuery though.

       

      # Rank Top 2 = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            >= 2
    )
VAR ValidPoliciesMinPremiums =
    GENERATE (
        ValidPolicies,
        CALCULATETABLE (
            // Top 2 Premiums for this policy, across all selected Companies
            TOPN (
                2,
                SELECTCOLUMNS ( Quotes, "Prem", Quotes[Premium] ),
                [Prem], ASC
            ),
            // Alternative:
            // TOPN ( 2, VALUES ( Quotes[Premium] ), Quotes[Premium], ASC ) // This would return top 2 distinct premiums
            ALLSELECTED ( Companies )
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Quotes[PolicyLinkID] ), ValidPoliciesMinPremiums )
      Average $ in First = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            >= 2
    )
RETURN
    AVERAGEX (
        ValidPolicies,
        VAR PremiumForSelectedCompany =
            CALCULATE ( MIN ( Quotes[Premium] ) ) // Should be a single value for a given Company
        VAR MinPremiumOverall =
            CALCULATE (
                MIN ( Quotes[Premium] ),
                // Minimum Premium for this policy, across all selected Companies
                ALLSELECTED ( Companies )
            )
        RETURN
            IF (
                PremiumForSelectedCompany = MinPremiumOverall,
                VAR SecondMinPremiumOverall =
                    CALCULATE (
                        MIN ( Quotes[Premium] ),
                        ALLSELECTED ( Companies ),
                        Quotes[Premium] > MinPremiumOverall
                    )
                RETURN
                    SecondMinPremiumOverall - PremiumForSelectedCompany
            )
    )

      For the Average $ in First by Premium I wasn't 100% sure I understood the calculation, so could you please show an example of the expected result based on your sample data?

       

      Best regards,

      Owen

      • amotto11's avatar
        amotto11
        Helper II
        8 years ago

        OwenAuger

         

        Thank you again for your help! I believe the Rank Top 2 is working, but i have some more testing i need to do for it as i have not been able to work on this project the last couple of days.

         

        As for the Average $ In First, you have the Average dollar amount that a company is winning by. I would also like to show the average % that they are winning by. The best way i think to do this would be subtract the first place companies premium from the second and divide the difference by the first place companies premium, it would look like this with just D and F selected:

         

        CompanyName    Quote Count    # Rank 1    # Rank 2   Average $ In First    Average % in First

            CompanyD              2                       1                2                  $50                             50.0%

            CompanyF               2                       1                2                  $150                           21.4%

         

        With all companies selected:

        CompanyName    Quote Count    # Rank 1    # Rank 2   Average $ In First    Average % in First

            CompanyA              2                       1                2                  $50                              100.0%

            CompanyB              2                       2                2                  $75                                 58.3%

            CompanyC              1                       0                0                  $0                                    0.0%

            CompanyD              3                       0                1                  $0                                    0.0%

            CompanyF               2                       0                1                  $0                                    0.0%

         

        The 58.3% is coming from (((100-50)/50) + ((700-600)/600))/2

         

        It may be more accurate to do the weighted average difference in other words sum all of the difference in premiums when winning for a particular company and subtract them from the second place company on all winning quotes and divide by the total premium on all winning quotes, so that all companies selected would look like this:

         

        CompanyName    Quote Count    # Rank 1    # Rank 2   Average $ In First    Average % in First

            CompanyA              2                       1                2                  $50                              100.0%

            CompanyB              2                       2                2                  $75                                 23.1%

            CompanyC              1                       0                0                  $0                                    0.0%

            CompanyD              3                       0                1                  $0                                    0.0%

            CompanyF               2                       0                1                  $0                                    0.0%

         

        The 23.1% is coming from (50+100)/(50+600)

         

        One more thing that i came across that i am struggling with now that i am selecting companies to rank against each other. I would like to have a seperate slicer on another tab that focuses in on one company to look at their win rate, rank top 2 etc based on different variables in the policy table such as county, zip code etc. I want to focus on one company but keep the companies that i have selected to force the rankings. Basically I would like one slicer to control what companies are being compared against one another as a report filter, and one page level slicer that filters it down to a single company within the group of companies selected so that i can view their statistics on a more granular level agains the companies selected in the report filter. Do you know if there is any way to do this?

         

        Thank you again for your continued help, it is much appretiated!!