Create Measure that Counts Total Rows when added to Table

patshannon11 — Mon, 24 Jun 2024 22:37:02 GMT

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:

ZIP	Facility 1	Market Total
10001	52	150
10002	88	156
10003	96	125
10004	15	173
10005	85	155
10006	84	141
10007	12	166
10008	85	157
10009	64	178
10010	35	190
10011	44	120
10012	50	101
10013	87	192
10014	65	178
10015	37	152
10016	85	144
10017	24	167
10018	65	137
10019	77	168
10020	90	147

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.

Re: Create Measure that Counts Total Rows when added to Table

Irwan — Tue, 25 Jun 2024 00:58:58 GMT

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.

Re: Create Measure that Counts Total Rows when added to Table

patshannon11 — Tue, 25 Jun 2024 20:33:14 GMT

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%.

topic Create Measure that Counts Total Rows when added to Table in DAX Commands and Tips

Create Measure that Counts Total Rows when added to Table

Re: Create Measure that Counts Total Rows when added to Table

Re: Create Measure that Counts Total Rows when added to Table