How to create aggregate counts multiple times on the same field in a table

[Anonymous]

Hey! I'm pretty new with Power BI and I'm struggling to create a specific table in a report. I have data that looks like this:

And want to create a table that looks like this:

Any help would be much appreciated. Thanks!

[m13eam · Accepted solution]

Hi - if you were happy to make some compromises and were happy for it to look like the below, then it's fairly simple to do:

To do this, and if your 2 column table was in a query called "Extract":

1. Create a measure with this DAX expression: CategoryCount = COUNT('Extract'[Value])
2. Create a "Matrix" visual
3. Within the visual,
   1. Put "Category" into Rows
   2. Value into Columns
   3. CategoryCount into Values

4. Under "Format your visual", turn off "Column subtotals" and "Row subtotals"

If you need the column headers to be prefixed with "Count" and the blanks to show as 0 then it is possible, but requires extra work. I'm happy to help if that's the case, but hopefully the above is a good start!

Let me know how you get on, or if I've presumed knowledge with creating queries/tables/measures you haven't gained yet!

Thanks,

Matt