Total by date category

[pieterhkruger]

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?

[v-lili6-msft · Accepted solution]

hi, @pieterhkruger 

Just try this formula to add a measure

Measure = CALCULATE(COUNTA(Table1[Group]),ALLEXCEPT(Table1,Table1[Date]))

Result:

Best Regards,

Lin

[jthomson]

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