Count inside a virtual table

[Iamnvt]

hi,

I have a table, and need to summarize for few key columns:

ProductCategorySales

A	AB	1
B	AB	2
C	AB	3
D	BB	33
E	BB	4
D	BB	1

How can I get the summarize from Product, Category, and an addtional column: count the rows of Category in that summarize table?

Expected output:

CategoryProductCount of Category

AB	A	3
AB	B	3
AB	C	3
BB	D	2
BB	E	2

here is the PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk8N44FWQEfeQbFXbnA?e=Y5tTDh

Thanks

[camargos88 · Accepted solution]

Hi @Iamnvt ,

Try this code:

Table 2 =

VAR _tb = SUMMARIZE('Table','Table'[Category],'Table'[Product])

RETURN ADDCOLUMNS(_tb, "Count of Category", COUNTX(FILTER(_tb, 'Table'[Category] = EARLIER('Table'[Category])), 'Table'[Category]))

[amitchandak]

@Iamnvt , try a measure like this, you can use this in summarize or summarizecolumns

Measure = calculate(count(Table[Category]),allexcept(Table[Category]))