Sum the results of a distinct count by date (or other category)

[Anonymous]

Hello everyone. 

Is there a way I can take a distinctcount and sum the values that comes from it dynamically to be used in a calculation?  The results would need to change by date ranges that are being viewed.

My data is below but right now I'm using this measure in DAX:

Calculate(
DistinctCOUNT(Fact[VehicleID]))

When I pull this into a table and I group it week to week it is working fine.  The issue is the "total" is also doing a select distinct.  So say I pull a range of 4 weeks. 

Each week counts correctly where

week 1 is 30
week 2 is 28
week 3 is 33
week 4 is 24

But the toal is showing 36 since it is counting the distinct number of vehicles that occurred in that month.  

I want it to sum.  So the above example the total would be 115.  

Here is some example data.  I am going to do a similar calculation for Drivers as well but I am hoping I can utilize what is good for this for that.  

Table:							Results:
Driver1	VehicleID	BillDate	LocationCode					VehicleCount	BillDate	LocationCode
Bill	A	7/13/2019	HERE					2	7/13/2019	HERE
Bill	A	7/13/2019	HERE					3	7/13/2019	ELSE
Bill	A	7/13/2019	HERE					1	7/20/2019	HERE
Bill	A	7/13/2019	HERE
Jeff	B	7/13/2019	HERE
Jeff	B	7/13/2019	HERE					Month
Jeff	B	7/13/2019	HERE					VehicleCount	BillDate	LocationCode
Jeff	B	7/13/2019	HERE					3	July	HERE
Jeff	B	7/13/2019	HERE					1	July	HERE
Jane	B	7/13/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Jane	A	7/20/2019	HERE
Bill	A	7/13/2019	ELSE
Bill	A	7/13/2019	ELSE
Bill	A	7/13/2019	ELSE
Bill	A	7/13/2019	ELSE
Jim	C	7/13/2019	ELSE
Jim	C	7/13/2019	ELSE					`
Jim	C	7/13/2019	ELSE
Jim	C	7/13/2019	ELSE
Jim	C	7/13/2019	ELSE
Jeff	C	7/13/2019	ELSE
Jeff	D	7/13/2019	ELSE
Jeff	D	7/13/2019	ELSE
Jeff	D	7/13/2019	ELSE
Jeff	D	7/13/2019	ELSE
Jack	E	7/13/2019	ELSE

[d_gosbell · Accepted solution]

So if week is the lowest date grain you could probably do something like the following looping over each BillDate and summing the distinct counts:

Distinct Vehicles per week = SUMX( VALUES('Table'[BillDate]), CALCULATE(DISTINCTCOUNT('Table'[VehicleID])))

I'm not sure if this meets your requirements or not as I get different values to what you show in your expected results. But you are not showing the expected grand total and I can't see how you got your results (eg. on 13 July I can see 4 different vehicle IDs for the ELSE location, not 3)