Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
achristopherson
Frequent Visitor

How to show total number of people from various zip codes?

Hello, I have an issue and I am wondering if someone can help. I have an applicant table that has name, address, zip, applicanti id, etc. But I need to show how many applicants are coming from each zip code. How can I do this? It seems like it should be an easy thing to do but I can't seem to figure it out. In simple terms I need to know: count of applicants by zip code. 

1 ACCEPTED SOLUTION
vega
Resolver III
Resolver III

You can put zip code into the rows and then names in the values column. From there select the arrow on the names tag and select count. Please see below:

 

Capture.PNGCapture1.PNG

View solution in original post

6 REPLIES 6
edhans
Super User
Super User

Take a look at this file. 

 

Basically you just need to use the COUNT() measure.

 

Name Count = COUNT('Names by Zip Code'[Names])

When you drop the zip codes and Name Count measure into a table, it will automatically give you the total per zip code.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

I can't get this to work. The table is called 'Applicant' and the fields are ApplicantID and ZIP. I keep getting a syntax error..

 

This is what I tried: Name Count = COUNT('Applicant'[ApplicantID] by 'Applicant'[zip])  and it doesn't work. 


@achristopherson wrote:

I can't get this to work. The table is called 'Applicant' and the fields are ApplicantID and ZIP. I keep getting a syntax error..

 

This is what I tried: Name Count = COUNT('Applicant'[ApplicantID] by 'Applicant'[zip])  and it doesn't work. 


FWIW, COUNT() only takes one argument, a column. The "By zip" would be taken care of in the filter context of the table. When you drop the COUNT() measure in the table next to the zip codes, the DAX engine goes down each row in the table and says "Ok, I need to count the names, but only for the row I am on, which is zip 12345" then it moves to the next row and does the same.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

It would need to be done this way:

 

CountMeasure = COUNT('Applicant'[ApplicantID])

Then, you place the zips as row value and this new measure as the values. It is the same as doing it through the UI. 

vega
Resolver III
Resolver III

You can put zip code into the rows and then names in the values column. From there select the arrow on the names tag and select count. Please see below:

 

Capture.PNGCapture1.PNG

This is exactly what I needed. Thank You. I used Applicant ID instead of name but otherwise I did exactly as you said. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.