Forum Discussion
Solved
Total by date category
Hi,
Using the following sample data:
| Date | Group | FILTER |
| 01-May-19 | A | Filter group 1 |
| 01-May-19 | A | Filter group 2 |
| 01-May-19 | A | Filter group 1 |
| 01-May-19 | B | Filter group 1 |
| 01-May-19 | B | Filter group 1 |
| 01-May-19 | B | Filter group 2 |
| 02-May-19 | A | Filter group 1 |
| 02-May-19 | A | Filter group 1 |
| 02-May-19 | A | Filter group 1 |
| 02-May-19 | A | Filter group 2 |
| 02-May-19 | A | Filter group 2 |
| 02-May-19 | A | Filter group 2 |
| 02-May-19 | B | Filter group 1 |
| 02-May-19 | B | Filter group 2 |
| 03-May-19 | A | Filter group 2 |
| 03-May-19 | A | Filter group 2 |
| 03-May-19 | B | Filter group 1 |
| 03-May-19 | B | Filter group 1 |
| 03-May-19 | B | Filter group 2 |
| 03-May-19 | B | Filter group 2 |
I want to be able to create a table that looks like this:
| Date | Group | Count of Group & date | Total count by date only |
| 2019/05/01 | A | 3 | 6 |
| 2019/05/01 | B | 3 | 6 |
| 2019/05/02 | A | 6 | 8 |
| 2019/05/02 | B | 2 | 8 |
| 2019/05/03 | A | 2 | 6 |
| 2019/05/03 | B | 4 | 6 |
I am particularly interested in getting the values for the last column. I suppose one would need to create a DAX measure. How can one go about doing so?
hi, pieterhkruger
Just try this formula to add a measure
Measure = CALCULATE(COUNTA(Table1[Group]),ALLEXCEPT(Table1,Table1[Date]))
Result:
Best Regards,
Lin
2 Replies
hi, pieterhkruger
Just try this formula to add a measure
Measure = CALCULATE(COUNTA(Table1[Group]),ALLEXCEPT(Table1,Table1[Date]))
Result:
Best Regards,
Lin
Use countrows on the table the same way you'd make the first column, but you want to apply allexcept to the date column - this'll make the calculation disregard the group (and anything else for that matter) but still split it by date
