Grouping by number of subitem

[Anonymous]

I have a dataset made of products and categories. For example a category "Beverages" has 5 types of drinks, while "food" has 7. I can easily show
Food : 7
Beverages : 5

Now imagine that I have several hundreds of categories, and some of them has 5 products, some 6,some 8 etc.
I would like to group by number of products per category, so something like
Categories with 7 products : 19
Categoriea with 6 products : 16
Categories with 5 products : 21 etc
Is is possibile with a standard visual without DAX?
And if with DAX I'm not sure how to write this...
Thanks!

[affan · Accepted solution]

Hi @Anonymous,

You can add a calculated column to the table as below

Category Count = VAR countr= COUNTROWS(FILTER(Table1,Table1[Category]=EARLIER(Table1[Category]))) 
Return "Categories with " & countr &  " products."

This will give you a count for the category against each row. Then you can use this column in  standard visuals like Matrix or Table.

I have generated a sample data with some retail categories and added products to each category. You can download the pbix from the link below and give it a try.

https://www.dropbox.com/s/tdi3elkayn3elrz/Category%20Count.pbix?dl=0

Please mark this post as an accepted solution if this helped you.

Regards,

Affan

[Anonymous]

Hi Affan

that is very helpful. The numbers I get are very weird but it's a very complicated model. So I got the logic behind it, now studying the EARLIER function.

thanks

[Greg_Deckler]

Can't think of a way with DAX. I think that the most straight-forward way with DAX would be to create a table like:

Table =
VAR __table = SUMMARIZE('Table',[Category],"__count",COUNT([Some Column]))
RETURN DISTINCT(__table[__count])

Something like that, didn't try it specifically. Then use this table column as your axis. I don't know, there's an issue somewhere in there I'm sure, will have to think about it.