Forum Discussion
Solved
Count inside a virtual table
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
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]))
2 Replies
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]))Iamnvt , try a measure like this, you can use this in summarize or summarizecolumns
Measure = calculate(count(Table[Category]),allexcept(Table[Category]))
