Create Measure that Counts and Ranks Another Measure and Updates when Filtered

[patshannon11]

My goal is to create a summary table containing a measure that scores each ZIP based on Facility 1 % Share, where the highest share has a score of 100%.  I would also like the scoring to maintain these rules when filtering on either ZIP or County.  I'm starting with a source data table similar to this:

ZIP	County	Facility	Volume
10001	County A	Facility 1	52
10002	County A	Facility 1	88
10003	County A	Facility 1	96
10004	County A	Facility 1	15
10005	County A	Facility 1	85
10006	County B	Facility 1	84
10007	County B	Facility 1	12
10008	County B	Facility 1	85
10009	County B	Facility 1	64
10010	County B	Facility 1	35
10001	County A	Facility 1	44
10002	County A	Facility 1	50
10003	County A	Facility 1	87
10004	County A	Facility 1	65
10005	County A	Facility 1	37
10006	County B	Facility 1	85
10007	County B	Facility 1	24
10008	County B	Facility 1	65
10009	County B	Facility 1	77
10010	County B	Facility 1	90
10001	County A	Facility 1	102
10002	County A	Facility 1	138
10003	County A	Facility 1	146
10004	County A	Facility 1	65
10005	County A	Facility 1	135
10006	County B	Facility 1	134
10007	County B	Facility 1	62
10008	County B	Facility 1	135
10009	County B	Facility 1	114
10010	County B	Facility 1	85
10001	County A	Facility 1	94
10002	County A	Facility 1	100
10003	County A	Facility 1	137
10004	County A	Facility 1	115
10005	County A	Facility 1	87
10006	County B	Facility 1	135
10007	County B	Facility 1	74
10008	County B	Facility 1	115
10009	County B	Facility 1	127
10010	County B	Facility 1	140
10001	County A	Facility 2	42
10002	County A	Facility 2	98
10003	County A	Facility 2	86
10004	County A	Facility 2	25
10005	County A	Facility 2	75
10006	County B	Facility 2	94
10007	County B	Facility 2	2
10008	County B	Facility 2	95
10009	County B	Facility 2	54
10010	County B	Facility 2	45
10001	County A	Facility 2	34
10002	County A	Facility 2	60
10003	County A	Facility 2	77
10004	County A	Facility 2	75
10005	County A	Facility 2	27
10006	County B	Facility 2	95
10007	County B	Facility 2	14
10008	County B	Facility 2	75
10009	County B	Facility 2	67
10010	County B	Facility 2	100
10001	County A	Facility 2	92
10002	County A	Facility 2	148
10003	County A	Facility 2	136
10004	County A	Facility 2	75
10005	County A	Facility 2	125
10006	County B	Facility 2	144
10007	County B	Facility 2	52
10008	County B	Facility 2	145
10009	County B	Facility 2	104
10010	County B	Facility 2	95
10001	County A	Facility 2	84
10002	County A	Facility 2	110
10003	County A	Facility 2	127
10004	County A	Facility 2	125
10005	County A	Facility 2	77
10006	County B	Facility 2	145
10007	County B	Facility 2	64
10008	County B	Facility 2	125
10009	County B	Facility 2	117
10010	County B	Facility 2	150

I first created a measure for Facility 1 % Share using the DAX below: 

Facility 1 % Share = 
DIVIDE(CALCULATE(SUM('Table'[Volume]), 'Table'[Facility] IN { "Facility 1" }),SUM('Table'[Volume]))

I then created a measure for Facility Share Rank using the DAX below:

Facility 1 Share RANK = 
RANKX(ALLSELECTED('Table'[ZIP]),[Facility 1 % Share],,ASC,Skip)

I then created a measure for ZIP Count using the DAX below:

ZIP Count = CALCULATE(MAXX('Table','Table'[Facility 1 Share RANK]),ALL('Table'))

Lastly, created a measure for Score using the DAX below:

Score = [Facility 1 Share RANK]/[ZIP Count]

The score works as intended without filters applied, but you can see in the 2nd and 3rd visual below when filtering by County or ZIP the score is not updating appropriately (in both cases scores should be 100%, 80%, 60%, 40%, and 20%).  It appears my ZIP Count is not interacting with the filters - would appreciate any advice there or open to other approaches as well.

[BeaBF]

@patshannon11 Hi!

Try with these measures:

Facility 1 % Share =

DIVIDE(

    CALCULATE(SUM('Table'[Volume]), 'Table'[Facility] = "Facility 1"),

    CALCULATE(SUM('Table'[Volume]))

)

Facility 1 Share RANK =

RANKX(

    ALLSELECTED('Table'[ZIP]),

    [Facility 1 % Share],

    ,

    DESC,

    Skip

)

ZIP Count =

COUNTROWS(ALLSELECTED('Table'[ZIP]))

Score =

DIVIDE([Facility 1 Share RANK], [ZIP Count])

BBF

[patshannon11]

Appreciate the feedback.  It looks like this will give expected results when I filter on ZIP, but not when I filter on County?

[BeaBF · Accepted solution]

@patshannon11 modify these two measures:

Facility 1 Share RANK =

RANKX(

    ALLSELECTED('Table'[County], 'Table'[ZIP]),

    [Facility 1 % Share],

    ,

    DESC,

    Skip

)

ZIP Count =

COUNTROWS(ALLSELECTED('Table'[ZIP], 'Table'[County]))

now it works also with County filter.

If it's ok, please accept the answer as solution.

BBF