cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Counting variable data within a list

Hello

 

Here's one Im stuck with.  I have 2 stores, each service people with different Citizen codes.  I want to be able to COUNT the number of times that citizen code appears in a list, but without hard coding EACH of the citizen codes. (the list is extensive, snapshot below)

 

At present I have a measure for each citizen code  // AUS = CALCULATE ( COUNTROWS (Sheet1),Sheet1[Citizen]="AUS") // but this means I need to create a measure for each citizen code individually, and the lists can grow regularly so its hard to keep up.

 

Is there a way that I can the system to recoginze the unique citizen code values in the list, then count those unique values, but without having to create individual measure for each citizen code?

 

ShopDateCitizen
PX14/5/2016AUS
PX24/5/2016AUS
PX14/6/2016AUS
PX24/6/2016AUS
PX24/6/2016AUS
PX14/6/2016AUS
PX14/6/2016AUS
PX24/9/2016AUT
PX14/9/2016AUT
PX24/9/2016DEU
PX24/9/2016DEU
PX14/9/2016DEU
PX14/9/2016DEU
PX24/9/2016DEU
PX14/9/2016DEU
PX24/10/2016DEU
PX24/10/2016DEU
PX14/10/2016DEU
PX14/11/2016NOR
PX24/11/2016NOR
PX14/11/2016NOK
PX24/11/2016NOK
PX24/11/2016DEU
PX14/11/2016CHL
PX14/11/2016DEU
PX14/12/2016DEU
PX14/12/2016GBR
2 ACCEPTED SOLUTIONS
Community Champion
Community Champion

Measure = CALCULATE ( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Citizen]) )

Count.png

View solution in original post

Community Champion
Community Champion

Yes the above will give OVERALL Total at the Citizen level no matter what - useful for calculating % at the Citizen level (NOT Grand Total)

 

Yes if you'll use Slicers just use Total = COUNTROWS(Table) with no filters!

 

Count2.png

 

Hope this helps and makes sense! Smiley Happy

View solution in original post

3 REPLIES 3
Community Champion
Community Champion

Measure = CALCULATE ( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Citizen]) )

Count.png

View solution in original post

Thanks @Sean, this is great.

 

 

Community Champion
Community Champion

Yes the above will give OVERALL Total at the Citizen level no matter what - useful for calculating % at the Citizen level (NOT Grand Total)

 

Yes if you'll use Slicers just use Total = COUNTROWS(Table) with no filters!

 

Count2.png

 

Hope this helps and makes sense! Smiley Happy

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors