Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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.
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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |