Count by Group

[PowerBIUser349]

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

[rsbin]

@PowerBIUser349 ,

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,

[PowerBIUser349]

I need to show the ProductID without the color

[rsbin]

@PowerBIUser349 ,

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.

[PowerBIUser349]

This is a better sample dataset... I only added two rows, but shows the problem I am facing with the dax formula you shared above. 

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
123	Red	1
124	Blue	1

[rsbin]

@PowerBIUser349 ,

Are you now saying your Table includes duplicates?

Product ID	Color	Count
123	Red	1
124	Blue	1
123	Red	1
124	Blue	1

Do you want these duplicates counted or not?

[PowerBIUser349]

Good question. And sorry for not explaining better. I still need it to return this:

Product ID	Group Count
123	4
124	3
125	3
126	4
127	2
128	4
129	2
130	4
131	3

Which means it would be a distinct count of Product ID, but showing the subtotal count for each color. 

[rsbin]

@PowerBIUser349 ,

So, I think you are looking for a new Calculated Table.

Unfortunately, I have to sign off, but will try to look at it later this evening or tomorrow for you.

Kind Regards,

[rsbin]

@PowerBIUser349 ,

Do not want to leave you hanging if this issue is still unresolved.  Would like to try to clean this up if need be.  I think the first step is to get this calculated column from my original response working for you.

ColorCount = CALCULATE( COUNTA([Color]), ALLEXCEPT( 'Product', 'Product'[Color] ))

If possible, please attach a sample pbix file and I can try to work with that.

Regards,

[PowerBIUser349]

Nevermind... I don't have the option to attach files. See this link: https://drive.google.com/file/d/1kDUwCJccvUTnm-czvExzVi9t8YUXWyo4/view?usp=sharing

[rsbin]

@PowerBIUser349 ,

Think I got it straightened out now.  My original reply was to create a Calculated Column.  You inadvertently created a Measure.  That is why it was not working the way I intended.

Please review the attached with my corrections.  I hope this now resolves the issue.

Best Regards,

[PowerBIUser349]

I see. Unfortunately this doesn't work for my actual data... it works for this scenario, but my dataset is much more granular and this formula is counting every row in the table.  

[PowerBIUser349]

I am still trying to figure it out. I attached a pbix file to the orignial post. Thank you for your help. 

[PowerBIUser349]

Dang... my actual dataset is much more complex and I am not able to do that... the ProductID and Color was too simple. 

[mangaus1111]

Hi @PowerBIUser349 , I don' t understand what you mean by Group Count

[PowerBIUser349]

See example.