topic Re: DAX Expression to Count how many sales people have met or exceeded their sales quota for 2023 in DAX Commands and Tips

DAX Expression to Count how many sales people have met or exceeded their sales quota for 2023

DeanUW — Fri, 28 Jul 2023 15:50:02 GMT

Hello,

I have a fact table that contains all of the sales transactions for all of the sales people (star schema with sales in one table, sales agents in another, and office data in a third merged into one central fact table). I need to count how many sales people have a YTD sales total that exceeds there 2023 sales threshold. The columns in the fact table include:

[Person ID] = unique key, will appear multiple times in the table for each sale.

[Gross Amount] = calculated column that is simply [credits] - [debits] for every sales line. it is not calculating YTD total only the gross for each sale.

[2023 Threshold] = different values are given to different sales people.

[postingDate] = the date the sales transaction occured.

The co-pilot feature has not been much help creating a DAX expression that will count the distinct [Person ID] whose YTD total >= [2023 Threshold]; & my DAX skills are not quiet advanced enough. Every attempt I have made only ends up counting the people who had a single transaction above their quota not their YTD total sales amount.

Thank you in advance to anyone who is able to assist!

Re: DAX Expression to Count how many sales people have met or exceeded their sales quota for 2023

Take3 — Fri, 28 Jul 2023 17:23:45 GMT

DeanUW

Please try the following.

NOTE: I created the below test Fact table. As you can see from the data, Ed & Joe met their threshold, while Sarah did not. For this reason, 2 is the correct number of persons who exceeded their threshold for 2023.

Regards,

Nathan

P.S. I used DAX to create my own Calendar table for Time Intelligence functions to work.