Forum Discussion
Count by Group
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 |
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?
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.
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,
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,