Count Days where Any Salesperson in a Department Exceeds a Sales Threshold

I am trying to write some DAX to allow me to count the number of days where an individual salesperson in a department exceeds some threshold.
If the entire data set were as follows (random individual sales values between 10 and 20 for 5 fictitious sales people for 7 days):


Then I can visualise this simply enough and see that there are 4 days where an individual sales person exceeded daily sales total of 20:

What I’d like to have is a measure which counts the number of times any individual salesperson has had total daily sales exceeding that threshold (in this case 20).
Aggregating this measure by department level for this date range should be 4, and at an individual level should be 1 each for Salespersons 2,3,4, and 5, and 0 for Salesperson #1.

Why I want this is I can then see how often anyone in the department is doing this (percentage of days where this happened), and how often individuals achieve this over certain timeframes.

This seems such an obvious problem to me that you financial/sales types would have come across before but hours of searching has got me no closer. I’m in health care, so I think that I am probably just using the wrong jargon for my search terms. I’m looking at completely different data, but the structure is the same, so any solution you can give me for this will work for my data.

The (trivial) source data is attached.

Any Help gratefully received,
Rod

DummyData.csv (1.1 KB)

Hi @rodwhiteley

See if this works for you.

I’ve built a simple data model with the supplied sample and added departments.
image

Next created these 3 measures:

Threshold value = 20

Total sales = SUM( Sales[Sales Amount] )

Count days over treshold = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            SUMMARIZE( Sales, Departments[Department], Departments[Salesperson], Dates[Date] ),
            "@Sales", [Total sales]
        ),  [@Sales] >= [Threshold value]
    )
)

With this result

image

Here’s your sample
eDNA - Count days over threshold.pbix (81.0 KB)

I hope this is helpful

That measure is a thing of beauty, and I was able to translate to my model first time :slight_smile:


Thanks again - I really need to learn more about SUMMARIZE & ADDCOLUMNS, but you’ve got me out of a pickle here, so please take a pat on the back out of petty cash :heart:
Rod