Count distinct at daily level and then aggregate (sum) this figure to monthly and year level.

[Sarakoth]

Hi Everyone,

I have tried finding an answer to this and many things come close but I can't quite nail it.

Essentially I have some salesmen, that accept orders at a location. I am trying to determine the number of distinct times a order has been made by a salesmen at each location on a daily level. I then want to aggregate this figure up to a monthly or yearly level.

So if a salesmen makes multiple orders at a location, it is only counted as 1. 

My chart displays my distinctcount measure correctly at the daily level, but when I try to display at monhtly or yearly level is applies the distinctcount at that level and is therefore wrong.

Some sample raw data is:

Date	Salesmen ID	Location ID	Order ID
4/08/2023	1	55	1001
4/08/2023	1	55	1002
4/08/2023	2	55	1003
4/08/2023	3	77	1004
4/08/2023	3	77	1005
10/08/2023	1	55	1006
12/08/2023	2	77	1007
12/08/2023	2	77	1008
12/08/2023	3	77	1009
12/08/2023	1	55	1010

So the daily values I am expecting would be:

Daily	Location	Distinct Count Salesmen
4/08/2023	55	2
4/08/2023	77	1
10/08/2023	55	1
12/08/2023	55	1
12/08/2023	77	2

So noticing that on the 4th Aug, 5 orders are reduced to a distinct count of salesmen by location for a result of 3.

Then I wish to simply show these on a chart effectivly like this:

Monthly	Location	Count
Aug-23	55	4
Aug-23	77	3

So August would totals 7, not the underlying 10 orders.

Any suggestions would be much appreciated.

[JoeBarry]

Hi @Sarakoth 

As the Sales Man ID should be visible with each sale, we will use this to count

Unique Sales per Visit = DISTINCTCOUNT(Sales[SalesManID])

In a Table you can add  the Date then the location and then the new Measure. 

Similar if you want to see how many total sales there are 

Total Sales = DISTINCOUNT(Sales[OrderID])

Thanks

Joe

If this post helps, then please Accept it as the solution

[Sarakoth]

Hi Joe,

Thanks for your suggestion. 

However I don't believe that works as I need as I roll up from daily to monthly.

Here is an example (not the exact same data as I gave above but hopefully still makes sense). 

Here are the orders at a daily level. 

You can see a total of 7 in August.

However on the 4th of August, the same salesmen had 2 orders in the same location.

So when I look for distinct on a daily level (using the measure definition you gave), the following chart is CORRECT.

So on the 4th, there were 3 different salesmen with orders in Fremantle. You can see this adds up to 6, and not 7.

Yet if drill up to monthly level (which is what I want to display), my chart now looks like this:

Which is WRONG.. it has now selected distinct combinations of locations and salesmen for the whole month, so is showing 4, not 6.  (2 salesmen have orders at the same location during the month so these are being dropped). The same problem would occur if I drill up to yearly level.

I am not sure if I can change or add items to the chart to make this work or I feel the measure needs to changed.