5 Replies Latest reply on Jul 27, 2018 8:10 AM by Jalen Asperger

# Count Number of Events Based on Multiple Criteria

Hello,

I have a map of census tracts and I am attempting to map a rate of the number of events per birth by year by census tract (geoid).

Each row in my dataset is an event, so I want to count each row that meets the criteria of fitting into census tract X (geoid) and year Y. I have a parameter for selecting what year to display on my map and I would like the map to update accordingly.

I know I need to be working with LOD calculations (or so I think), I'm just not sure exactly what steps to take.

Basically, I need something like:

IF [Year Selector] = "2009" THEN (Number of Events for 2009 for this Census Tract) / [2009 Births]

I think I could create a calculated to count the number of events for each year, but that seems cumbersome and wasteful when (I think) it could be done with one calculated field.

Thanks for the help!

I will attach an packaged workbook to give you an idea of what I'm working with.

• ###### 1. Re: Count Number of Events Based on Multiple Criteria

Hi Jalen,

You need to pivot your data to get a single calculated field

Kindly check the below link how to pivot the data ,

Create a calculation based on Pivot Names so that you will be able get all the values w.r.to single calc

BR,

NB

• ###### 2. Re: Count Number of Events Based on Multiple Criteria

I don't believe pivoting is an option because my full workbook (not the stripped down example I attached) has a great deal of work in it with many calculations and other things that will get broken if I pivot.

• ###### 3. Re: Count Number of Events Based on Multiple Criteria

Does anyone else have any suggestions on a way to do this in one calculated field without pivoting?

Otherwise I am open to doing it with multiple calculated fields.

• ###### 4. Re: Count Number of Events Based on Multiple Criteria

Hi, Jalen

Please find my solution attached.

A bit of hard code it as you can't pivot the data.

Hope this helps

ZZ

1 of 1 people found this helpful
• ###### 5. Re: Count Number of Events Based on Multiple Criteria

This worked! I had to tweak the calculation just a small amount to be:

ZN(SUM(IF INT([Year Selector]) = [Year] THEN ({INCLUDE [Geoid] : COUNT([BW])}) END))

/

SUM([birth])

instead of [BW] because I needed the count of events for each year, while BW is a measure that is irrelevant to this calcuation, but I had been using the count for my calculations, but I didn't specify that.

I appreciate the help!!