Need help creating matrix of counts for column values

[ElliotGPro]

Okay so I did some digging and got no hits with the search so here's hoping that someone on here might be able to give me a hand. So I have the below table currently, it is a matrix of a bunch of people and a bunch of categories, and how many numbers each person has in each category. This is very simplified data, but I can't post any of the raw data for privacy reasons.

Basically what I am trying to do is rather than see how many of each category each person has, I want to know how many people have that many numbers asssociated with the category. I just did a quick manual one to show the basic output I'm trying to get with this simple test data. I can't do this manually for the actual data, since there is about 20 categories and over 80000 people, so it really isn't feasible.

I tried a couple different ways like putting the numbers as the columns and creating a measure to put the to force it to be count, but neither of those worked since (as I found out from experimenting with this) you can't have aggregates as column values, or at least you can't how I tried to. I am still pretty new to BI in general and am the only analytics member in my team, so any help from the wider fabric community would be much appreciated. I also can't figure out how to upload my excel file directly, so I'm just going to put the table with the data in it directly into this post at the bottom. Apologies if that isn't the best way to do that.

Thanks in advance,

Elliot

Data:

Person	Category	Number
A	Cat 1	1
B	Cat 1	2
C	Cat 1	3
D	Cat 1	4
E	Cat 1	5
F	Cat 1	6
G	Cat 1	7
H	Cat 1	8
A	Cat 1	9
B	Cat 1	10
C	Cat 1	11
D	Cat 1	12
E	Cat 1	13
F	Cat 1	14
G	Cat 3	15
H	Cat 3	16
A	Cat 3	17
B	Cat 3	18
C	Cat 3	19
D	Cat 3	20
E	Cat 3	21
F	Cat 3	22
G	Cat 3	23
H	Cat 3	24
A	Cat 3	25
B	Cat 3	26
C	Cat 3	27
D	Cat 4	28
E	Cat 4	29
F	Cat 4	30
G	Cat 4	31
H	Cat 4	32
A	Cat 4	33
B	Cat 4	34
C	Cat 4	35
D	Cat 4	36
E	Cat 4	37
F	Cat 4	38
G	Cat 4	39
H	Cat 4	40
A	Cat 2	41
B	Cat 2	42
C	Cat 2	43
D	Cat 2	44
E	Cat 2	45
F	Cat 2	46
G	Cat 2	47
H	Cat 2	48
A	Cat 2	49

[ElliotGPro · Accepted solution]

I have since solved this problem, and I'll just write a quick solution on here in case anyone comes across it with the same problem.

Basically my initial data needed to be placed into a group by function. Turns out, Power BI has a similar function called SUMMARIZE. Nifty little calculation but by creating a new table that uses SUMMARIZE to summarize by person and category, and counting how many rows of those groups there are, it gives a table that can be used to generate the matrix that I have pictured above in my intial question. The table function should look something like the below code snippet:

GroupBy4 = SUMMARIZE(TableName, TableName[Category], TableName[Person], "Count of People", COUNT(TableName[Person]))

[Ritaf1983]

Hi @ElliotGPro 
You just need to put the numbers as "columns" 
and then you can use a simple discount measure :

Ther measure :

count_ = DISTINCTCOUNT('Table'[Person])

 I slightly modified your data to enable counting since you have only one value for each number

PBIX is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[ElliotGPro]

You have missed what I am looking for. Those numbers are all unique, I am looking to count how many of them exist for the person. The numbers themselves don't matter, only how many of them there are. Also you don't need a measure to create a distinct count, you can just put the people into the values column and change it from count to distinct count.

[Ritaf1983]

When someone "misses" something in our question, it could be that we haven't expressed ourselves properly. Therefore, blaming someone who is trying to help in their free time without any compensation is less likely to lead you to a solution.

Instead of getting angry, I would think about clarifying the question in a more understandable way. At least to me, it's still not clear.

Regarding "no measures are needed," the efficient way to work with Power BI is to build measures. This allows for flexibility as you continue to work, sometimes prevents errors, and improves performance.

Good luck,

Rita

[ElliotGPro]

Sorry, yes of course, we are all volunteers. I didn't mean to come of quite as snarky as I did in that last reply but yeah re-reading, probably not the best way to respond.

As for how to make it clearer, I did put an image of what I'm trying to get to in my main post and did mention that those numbers are meant to be unique, they aren't supposed to be duplicates like you made them in the data you put into your table, though ultimately they could just as much be all the same number, it is just a value there to represent that this person has a record matching this event.

The first image is of how many records a person has in each event. I am trying to show how many people have that same number of claims in that event in the second matrix. So for the Cat 1 category, there were 2 people that had 1 record with that category, and 6 people that had 2 records with that category. That's what I'm trying to represent, hope that makes it slightly clearer.

Oh and thanks for the tip with the measures, I didn't realise it was more efficient to make a measure for the distinct count rather than use the distinct count object when it is in the values cell.

Elliot