Counting a Rank with Conditions

[amotto11]

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 policylinkID (This is the unique quote number), companyID, Premium, and some other details around the quote. The table is such that there are multiple companyID's on each policylinkID. The second table is a unique table of companies with columns CompanyID (unique to this table), CompanyName. What I am trying to do is rank the companies for each quote by premium lowest to highest. Then count the number of times, number of quotes, each company is ranked #1. The trouble i am having is two fold. Each company is not quoted on every quote, so i am having to use hasonevalue or something like that in my formuals. The second and major road block i am having is that i want the user to be able to select the companies that he wants to look at (compare against each other) and the ranking function/counting function should dynamically change to only rank/count the companies that are being selected in the slicer. Taking that one step further, to add in credibility, I only want to rank/count quotes where the number of companies is greater than or equal to say 2. This way it won't rank if there is only one company on the quote. If the user selects 2 or more companies, it will only rank the quotes where there are 2 or more companies on the quote, otherwise the ranking/counting will not take place. Below is a summary of my tables:

PolicyLinkID     CompanyID    Premium

          1                     1                100

          1                     2                  50

          1                     3                 150

          1                     4                 200

          2                     2                 600

          2                     4                 850

          2                     6                 700

          3                     1                   50

          3                     4                 100

          3                     6                 150

CompanyID        CompanyName

          1                    CompanyA

          2                    CompanyB

          3                    CompanyC

          4                    CompanyD

          5                    CompanyE

          6                    CompanyF

If all companies were selected in the CompanyName slicer the results table would look like this:

CompanyName  Quote Count   # Rank 1

    CompanyA                2                   1

    CompanyB                2                   2

    CompanyC                1                   0

    CompanyD                3                   0

    CompanyF                 2                   0

If only CompanyD and CompanyF were chosen on the slicer the results would look like this

CompanyName      Quote Count     # Rank 1

     CompanyD                  2                    1

     CompanyF                   2                    1

I am sure this is going to be a multi phase answer possibly including formulas for the following:

1. Counting the number of selected companies on each quote and filtering is less than 2.
2. Ranking the companies selected where the quotes are acceptable based on formula 1.
3. Counting the number of quotes each company is quoted on based on formula 1.
4. Counting the number of times each company is ranked 1 on quotes based on formula 1 and 2.

Any help would be greatly appretiated!

[OwenAuger]

@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]

@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]

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]

@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!!

[amotto11]

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.

[Ashish_Mathur]

Hi,

When all companies are selected, why should Companies D and F not show up.  They have a count of more than >1.  Also, if Companies D and F are selected, why should the Rank be 1 for both?

[amotto11]

Ashish

Your first point is correct, and i have changed my original post to reflect that. With no companies selected, any that are included in the quotes should show up with a quote count, regardless if they have a rank 1. To your second point, why are D and F both Rank 1 when only they are selected. The reason is that Company D is on all 3 quotes, but company F is only on quote 2 and 3. So with the criteria of >= 2 companies being on a quote, only quotes 2 and 3 would be acceptable. Company F beats Company D on quote #2 but company D beats company F on quote #3, so each has one rank 1. I hope that helps.