Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BassG
Frequent Visitor

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

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

193
233
314
47
59
61
73
80
91
101

 

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?

1 ACCEPTED SOLUTION

Hi @BassG ,

 

Is it the expected result?

vstephenmsft_2-1696318548591.png

And this is the raw data.

vstephenmsft_1-1696318489259.png

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.           

View solution in original post

3 REPLIES 3
v-stephen-msft
Community Support
Community Support

Hi @BassG ,

 

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

Sample data:

vstephenmsft_0-1695951621828.png

Then select Count for how the ID field is displayed.

vstephenmsft_1-1695951661876.png

 

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.           

 

 

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...

IDCount of Case Ref
Joanna1
Bob1
Dave2
Mary1
Julie4
Steve2
Bill4
Graeme1
Phyllis2
Jill3
Fiona4
Peter2


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 refNumber of ID
14
24
31
43

 

Hi @BassG ,

 

Is it the expected result?

vstephenmsft_2-1696318548591.png

And this is the raw data.

vstephenmsft_1-1696318489259.png

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.           

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors