cancel
Showing results for
Did you mean:
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?

 Shop Date Citizen PX1 4/5/2016 AUS PX2 4/5/2016 AUS PX1 4/6/2016 AUS PX2 4/6/2016 AUS PX2 4/6/2016 AUS PX1 4/6/2016 AUS PX1 4/6/2016 AUS PX2 4/9/2016 AUT PX1 4/9/2016 AUT PX2 4/9/2016 DEU PX2 4/9/2016 DEU PX1 4/9/2016 DEU PX1 4/9/2016 DEU PX2 4/9/2016 DEU PX1 4/9/2016 DEU PX2 4/10/2016 DEU PX2 4/10/2016 DEU PX1 4/10/2016 DEU PX1 4/11/2016 NOR PX2 4/11/2016 NOR PX1 4/11/2016 NOK PX2 4/11/2016 NOK PX2 4/11/2016 DEU PX1 4/11/2016 CHL PX1 4/11/2016 DEU PX1 4/12/2016 DEU PX1 4/12/2016 GBR
2 ACCEPTED SOLUTIONS
Community Champion
`Measure = CALCULATE ( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Citizen]) )`

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!

Hope this helps and makes sense!

3 REPLIES 3
Community Champion
`Measure = CALCULATE ( COUNTROWS('Table'), ALLEXCEPT('Table', 'Table'[Citizen]) )`

Helper III

Thanks @Sean, this is great.

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!

Hope this helps and makes sense!

Announcements

#### Happy New Year from Power BI

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