Counting a Rank with Conditions

Question

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&nbsp;policylinkID (This is the unique quote number),&nbsp;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.&nbsp;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&nbsp;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&nbsp;is only one company on the quote. If the user selects 2 or more&nbsp;companies, it will only rank the quotes where there are 2 or more&nbsp;companies on the quote, otherwise the ranking/counting will not take place. Below is a summary of my tables:&nbsp;PolicyLinkID&nbsp;&nbsp;&nbsp;&nbsp; CompanyID&nbsp;&nbsp;&nbsp; Premium&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 200&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;600&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 850&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 700&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 50&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 150&nbsp;CompanyID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CompanyName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CompanyA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CompanyB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CompanyC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CompanyD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CompanyE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CompanyF&nbsp;If all companies were selected in the CompanyName slicer the results table would look like this:CompanyName&nbsp;&nbsp;Quote Count&nbsp;&nbsp; # Rank 1&nbsp;&nbsp;&nbsp; CompanyA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp; CompanyB&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp; CompanyC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp; CompanyD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;&nbsp;&nbsp;&nbsp;CompanyF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 0&nbsp;If only CompanyD and CompanyF were chosen on the slicer the results would look like thisCompanyName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Quote Count&nbsp;&nbsp;&nbsp;&nbsp; # Rank 1&nbsp;&nbsp;&nbsp;&nbsp; CompanyD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp; CompanyF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;I am sure this is going to be&nbsp;a multi phase answer possibly including formulas for the following:Counting the number of selected companies on each quote and filtering is less than 2.Ranking the companies selected where the quotes are acceptable based on formula 1.Counting the number of quotes each company is quoted on based on formula 1.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 · Answer

amotto11Here is an attempt at it:(pbix uploaded for reference)&nbsp;Note: I have named the tables Quotes and Companies.&nbsp;Quote Count = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            &gt;= 2
    )
RETURN
    COUNTROWS ( ValidPolicies )# Rank 1 = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            &gt;= 2
    )
VAR ValidPoliciesMinPremiums =
    GENERATE (
        ValidPolicies,
        CALCULATETABLE (
            FIRSTNONBLANK ( Quotes[Premium], 0 ),
            ALLSELECTED ( Companies )
        )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Quotes[PolicyLinkID] ), ValidPoliciesMinPremiums )In both measures, the&nbsp;ValidPolicies variable stores those PolicyLinkID values that have 2+ Companies.In the # Rank 1 measure, the ValidPoliciesMinPremiums&nbsp;variable&nbsp;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.&nbsp;I have used DISTINCTCOUNT ( Quotes[PolicyLinkID] ) in # Rank 1, just as a precaution in case their were duplicate quotes for&nbsp;the same company on one policy.&nbsp;One question I had was: In your sample outputs, you only showed Quote Count&nbsp;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.&nbsp;Regards,Owen

ashish_mathur · Answer

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

owenauger · Answer

Hi again&nbsp;amotto11&nbsp;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.&nbsp;You will need to verify &amp; test with DirectQuery though.&nbsp;# Rank Top 2 = 
VAR ValidPolicies =
    FILTER (
        VALUES ( Quotes[PolicyLinkID] ),
        CALCULATE ( DISTINCTCOUNT ( Quotes[CompanyID] ), ALLSELECTED ( Companies ) )
            &gt;= 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 ) )
            &gt;= 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] &gt; MinPremiumOverall
                    )
                RETURN
                    SecondMinPremiumOverall - PremiumForSelectedCompany
            )
    )For the&nbsp;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?&nbsp;Best regards,Owen

amotto11 · Answer

Ashish&nbsp;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 &gt;= 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.

amotto11 · Answer

Owen,&nbsp;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.

Forum Discussion

Counting a Rank with Conditions

7 Replies

Related content

Rank with conditions

Duplicate ranking

Count rows with condition

Ranking KPI card

Counting Ranks