Count number of times X appears & put that into number categories?

[BassG]

Hello again,

I have something in excel that I wondered if is possible in PBI...
I want to show how many times an ID appears & put that into order.  E.g. 93 IDs have 1 Case Ref, 33 IDs have 2 Case Refs, 14 IDs have 3 Case Refs, etc

1	93
2	33
3	14
4	7
5	9
6	1
7	3
8	0
9	1
10	1

In excel, I have a table  that also feeds PBI with the fields "ID" & "Case Ref" (amongst others).  with this I have a pivot table that has ID in Rows & Case Ref in Values (distinct count).  I then have a separate section on the sheet that looks just like the piece I've pasted above - 1-10 in column E & a formula in column F.  The formula is a simple countif ("COUNTIF($B$2:$B$971,E1" in F1, "COUNTIF($B$2:$B$971,E2" in F2 & so on) to let me see how many times a case ref appears for each number.

Is there a way to do this in PBI?

[v-stephen-msft]

Hi @BassG ,

Just try puting this two field in a table visual in Power BI Desktop.

Sample data:

Then select Count for how the ID field is displayed.

If the ID and Case Ref come from different tables, it doesn't matter, as long as there is a relational connection, it will work.

Best Regards,

Stephen Tao

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

[BassG]

Thanks.

Unfortunately, that's only the first 1/2 of what I want to do.
The ID is text.  So as an example, what I'll get is something like this...

ID	Count of Case Ref
Joanna	1
Bob	1
Dave	2
Mary	1
Julie	4
Steve	2
Bill	4
Graeme	1
Phyllis	2
Jill	3
Fiona	4
Peter	2

However, what I need to do with that is count how many people got 1, how many 2, how many s & so on.
So the end result should look something like this.

Count of Case ref	Number of ID
1	4
2	4
3	1
4	3

[v-stephen-msft · Accepted solution]

Hi @BassG ,

Is it the expected result?

And this is the raw data.

You can download my attachment for more details.

Best Regards,

Stephen Tao

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