Forum Discussion

patshannon11's avatar
patshannon11
Frequent Visitor
2 years ago

Create Measure that Counts Total Rows when added to Table

My goal is to create a summary table containing a measure that scores each ZIP based on facility % share, where the highest share has a score of 100%.  I'm starting with a source data table similar to this:

 

 

ZIPFacility 1Market Total
1000152150
1000288156
1000396125
1000415173
1000585155
1000684141
1000712166
1000885157
1000964178
1001035190
1001144120
1001250101
1001387192
1001465178
1001537152
1001685144
1001724167
1001865137
1001977168
1002090147

 

 

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

Facility % Share = DIVIDE(SUM('Table'[Facility 1]), SUM('Table'[Market Total]))​

 

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

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

 

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

ZIP Count = CALCULATE(COUNTROWS('Table'),FILTER('Table',[Facility % Share]))

 

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

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

 

My ZIP Count measure works fine when put into a card, but when I try to include in a table the count measure =1 based on the individual ZIP code in each particular row.   Is there a way to modify my ZIP count measure so that it will always =20 even when I bring it into the table below?  In other words I'd like the Score for 10003 to be 100%, 10020 to be 95%, 10006 to be 90%, etc.

 

 

2 Replies

  • Hello patshannon11 ,

     

    please check if this what you need.

     

    ZIP Count = CALCULATE(MAXX('Table','Table'[Facility Share RANK]),ALL('Table'))
     
    Hope this will help you.
    Thank you.
    • patshannon11's avatar
      patshannon11
      Frequent Visitor

      Thank you for the response.  One nuance I failed to mention was the count should also update based on filtering.  For example:  if I filter on ZIP 10001, 10002, 10003, 10004 ,and 10005 the count should update to 5 so that the highest Facility % Share still scores 100%.