Forum Discussion
Counting a Rank with Conditions
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
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!!