Forum Discussion
Filtering Bottom N Specialties by State Not Working
Sample dashboard: Here
My goal is to display Top 5 Specialties by Loss Ratio when a user selects a State on the map. Also, I want to display Bottom 5 Specialties by Loss Ratio (only where Loss Ratio > 0) when a user selects that State.
My data source was at the individual policy # level, but I've removed that column. Loss Ratio is calculated at the policy level as INCURRED / EARNED_PREMIUM.
I created a calculated column to get Loss Ratio using the following:
DIVIDE(Sheet1[INCURRED],Sheet1[EARNED_PREMIUM]). I then display Loss Ratio as an avg by State by Specialty. However, when trying to display Bottom 5 (for only Loss Ratios > 0), I don't get the results expected.
I then created a calculated measure to get Loss Ratio using the following:
DIVIDE(SUM(Sheet1[INCURRED]), SUM(Sheet1[EARNED_PREMIUM])). I then display Loss Ratio by State by Specialty. However, my Loss Ratios by State by Specialty are different than what the calculated column produced and I still don't get the results expected for Bottom 5 (for only Loss Ratios > 0).
I assume this is related to a lack of using the CALCULATE and/or FILTER functions, which is why I posted in this forum. I'm not educated enough in this area yet and wanted to see if anyone could assist.
Thanks in advance!
3 Replies
- amitchandak
Super User
mike_viz_lord , Try like
Top 5 = CALCULATE([Loss Ratio]),TOPN(3,ALL ('Table'[Specialties]),[Loss Ratio],DESC),VALUES('Table'[Specialties]))or
Specialties Rank = RANKX(all(Table[Specialties]),[Loss Ratio])
TOP Quantity = avergageX(filter(VALUES(Table[Specialties]),[Specialties Rank]=1),[Loss Ratio]) - wdx223_Daniel
Community Champion
mike_viz_lord this measure can get the same result
Loss Ratio Calc2 = AVERAGEX(Sheet1,DIVIDE(Sheet1[INCURRED], Sheet1[EARNED_PREMIUM])) - amitchandak
Any solution worked out for you?
Do the above replies help? if you need more help make me @
Appreciate your Kudos.
