Forum Discussion
Count by Group
Hey Everyone, I know this is going to be simple for someone experienced in PBI. I am still getting use to specific functions and could use some help. Here is a sample dataset:
| Product ID | Color | Count |
| 123 | Red | 1 |
| 124 | Blue | 1 |
| 125 | Blue | 1 |
| 126 | Red | 1 |
| 127 | Yellow | 1 |
| 128 | Red | 1 |
| 129 | Yellow | 1 |
| 130 | Red | 1 |
| 131 | Blue | 1 |
Then I am counting by each color group (My dax is currently doing this fine):
| Color | Count |
| Red | 4 |
| Blue | 3 |
| Yellow | 2 |
I need help getting this output:
| Product ID | Group Count |
| 123 | 4 |
| 124 | 3 |
| 125 | 3 |
| 126 | 4 |
| 127 | 2 |
| 128 | 4 |
| 129 | 2 |
| 130 | 4 |
| 131 | 3 |
15 Replies
Hi PowerBIUser349 , I don' t understand what you mean by Group Count
See example.
I believe this Calculated Column should get you what you need:
ColorCount = CALCULATE( COUNTA([Color]), ALLEXCEPT( 'Product', 'Product'[Color] ))Product IDColorCountColorCount
123 Red 1 4 124 Blue 1 3 125 Blue 1 3 126 Red 1 4 127 Yellow 1 2 128 Red 1 4 129 Yellow 1 2 130 Red 1 4 131 Blue 1 3 Regards,
I need to show the ProductID without the color
Once you have your Calculated Column in the data model, you can create any Visual that you want.
For a simple Table Visual, simply use your ProductID and ColorCount columns. You don't need to show the Color field in your visual.
