Creating Dynamic Ranking Tier Measure

[jrsdata]

Hi All-

Relatively new to Power BI.  I am looking to add a dynamic measure which would create a dynamic tiered ranking.  There should be 5 tiers.  For starters, each could comprise of 20%.  However, that % might change.  The Tier level will be based on the 'Policy Count' and will be aggregated by 'County'.  Tier 1 for example, will equate to all Counties that comprise of 20% of the total 'Policy Count'.

I believe i have to use a combination of EARLIER,RANKX, etc but not sure how to put together.

Few notes about the data.  Policy count is a measure created on a fact table field (Policy ID).  I will be using a filter on 'Product Type' to filter out type '2'.

Sample below:

County	Policy Count	Product Type	Tier#
Los Angeles County	57	1
Cook County	50	1
Harris County	60	1
Dallas County	100	1
Clark County	12	1
Wayne County	30	1
Cuyahoga County	40	1
San Diego County	21	1
Kings County	10	1
Riverside County	5	1
Maricopa County	4	1
Tarrant County	24	1
Philadelphia County	55	1
Maricopa County	20	2
Tarrant County	4	2
Philadelphia County	43	2

Appreciate all help and guidance!

Jared

[TomMartens]

Hey @jrsdata ,

you can create a measure that calculates the contribution of a county like so:

share of county = 
divide(
sum('<tablename>'[Policy Count])
, calculate(sum('<tablename>'[Policy Count]) , all('<tablename>'[Policy Count]))
)

Then you can this measure to retrieve the rank from a table, that you have to add to your data model and that contains the ranks´.

These articles provide guidance how to tackle this kind of segmentation tasks:

• https://www.daxpatterns.com/static-segmentation/
• https://www.daxpatterns.com/dynamic-segmentation/

Hopefully, this is what you are looking for.

Regards,

Tom

[jrsdata]

Hi Tom-

Tried implementing part 1 of your solution but seeing 1.00 across the board for 'share of county' measure.

[TomMartens · Accepted solution]

Hey @jrsdata ,

i forgot the 1st CALCULATE ...

this measure

Measure = 
DIVIDE(
    CALCULATE(SUM('Table'[Policy Count]))
    , CALCULATE(SUM('Table'[Policy Count]) , ALL('Table'[County]))
)

allows to create this little report:

Regards,

Tom