How to count occurrence of repeated text values in a column

[astokes]

Greetings,

I am new to Power BI and have encountered a snag that I would love help with troubleshooting.

I am working with a dataset from a user survey. Initially, I am concentrating on the Race and Region fields. I would like to display a "count" (not distinct) of how many times a standard text response occurs within a column by Region.

Here is what I would like the output to be:

Region	Count Asian	Count Black	Count White
Midwest	1	2	5
Northeast	2	5	4
Pacific	5	8	9
Southeast	8	7	7
Southwest	9	2	8
Total	25	24	33

Below is an example of the issue I am encountering: 

Region	Count Asian	Count Black	Count White
Midwest	1	1	1
Northeast	2	2	2
Pacific	5	5	5
Southeast	8	8	8
Southwest	9	9	9
Total	25	25	25

Please help and let me know if more information is needed?

Thank you.

A. Stokes

[v-yulgu-msft]

Hi @astokes,

Suppose your source table is like:

To display data in a matrix visual, please see below screenshot.

To show data in a clustered column chart visual, please see:

If you need to show count results in a table visual, you need to create measures like below:

Count Asian =
CALCULATE (
    COUNTROWS ( 'Region and race' ),
    FILTER ( 'Region and race', 'Region and race'[Race] = "Asian" )
)

Count Black =
CALCULATE (
    COUNTROWS ( 'Region and race' ),
    FILTER ( 'Region and race', 'Region and race'[Race] = "Black" )
)

Count White =
CALCULATE (
    COUNTROWS ( 'Region and race' ),
    FILTER ( 'Region and race', 'Region and race'[Race] = "White" )
)

Best regards,
Yuliana Gu

[astokes]

Hi @v-yulgu-msft,

Thank you for the tips.

Unfortunately, my data source looks like this:

Region	Asian	Black/African American
Northeast
Northeast	Asian	Black/African American
Northeast
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American
Northeast
Northeast
Northeast
Northeast
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American
Northeast
Northeast
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American
Northeast	Asian	Black/African American

I have this data source format for several survey response types (i.e., gender, ethnicity, etc.). Somehow, the counts in table visuals calculated properly for some values from some data sources, but not all of them. I'll create new measures instead as suggested and see if that resolves my issue. 

Thanks again. 

[v-yulgu-msft · Accepted solution]

Hi @astokes,

If your table structure is similar to below, you can convert it to the same structure as I posted above by unpivoting [Column1] and [Column2] in Query Editor.

After unpivot

Alternatively, if you don't want to change original table structure, you can create a calculated table.

New table = UNION(SELECTCOLUMNS(Test1,"Region",Test1[Region],"Race",Test1[Column1]), SELECTCOLUMNS(Test1,"Region",Test1[Region],"Race",Test1[Column2]))

For more advice, please provide more detailed sample data in your data source.

Regards,

Yuliana Gu

[astokes]

Duly noted to add more detail in the future. This did the trick. Thank you for helping me prevent pulling the rest of my hair out.

Have a great weekend!

Akissi

[Anonymous]

Is this a Table visualization or a Matrix?

If you use a Matrix with "Region" in the Rows section and "Race" in the Columns section, then your Count measure in the Values, it should work out.

[astokes]

Thank you. I will try that solution. Also, to answer your question - this will be both a table and chart visualization.