Forum Discussion
Total count by category and date
Hi Team,
I would like to know how we can return a total count based on the category and date.
Following is a matrix table of how the result should look like.
Thanks
| Category | 23-10-2021 | 24-10-2021 | 25-10-2021 | 26-10-2021 |
| A | 2 | 3 | 8 | 4 |
| B | 4 | 2 | 1 | 6 |
| C | 7 | 1 | 0 | 2 |
| Total | 13 | 6 | 9 | 12 |
Hi ankush-vem
Just use the following measure:
Measure = COUNTROWS ( 'Table' )
As you can see, COUNTROWS counts all of the rows in the table and allocates a Count to the respective category or date dependent upon how you want it presented:
There is no need for anything more complex and it gives you the correct total count in each of the totals (i.e. Row total and Column total).
All the best 🙂
Theo
3 Replies
- KNP
Super User
See attached PBIX.
If this is not what you need please provide some data to work with.
v-zhangtiCommunity Support
Hi,@ankush-vem
I simulated the data and calculated it to see if it can meet your needs.
Create a measure to calculate the quantity of each product on each date.
Count = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( ALL ( 'Table' ), 'Table'[Category ] = MAX ( 'Table'[Category ] ) && 'Table'[Date] = MAX ( 'Table'[Date] ) ) )Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more.
- TheoC
Community Champion
Hi ankush-vem
Just use the following measure:
Measure = COUNTROWS ( 'Table' )
As you can see, COUNTROWS counts all of the rows in the table and allocates a Count to the respective category or date dependent upon how you want it presented:
There is no need for anything more complex and it gives you the correct total count in each of the totals (i.e. Row total and Column total).
All the best 🙂
Theo
