Forum Discussion

patshannon11's avatar
patshannon11
Frequent Visitor
2 years ago
Solved

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

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 the...
  • BeaBF's avatar
    BeaBF
    2 years ago

    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
BeaBF's avatar
BeaBF
Super User
2 years ago

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's avatar
patshannon11
Frequent Visitor
2 years ago

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

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    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