topic Count the number of downline who meets certain criteria in DAX Commands and Tips

Count the number of downline who meets certain criteria

Adam00 — Tue, 12 Nov 2024 03:43:57 GMT

Hi there, hope everyone is doing well. I need help with creating a calculated column . The scenario is as such:-

I have a group of sales people (managers) who have their own downline of sales people(execs). All sales people need to hit a certain sales target to qualify for a certain incentive. How do I calculate the number of downline each manager has that qualifies for the incentive?

I have one column of all sales people including managers and execs and then another column showing the name of their manager (if the manager is the salesperson then his/her name will appear twice under the first and second column) the third column marks 1 or 0 depending on whether their sales target has been achieved.

Re: Count the number of downline who meets certain criteria

Poojara_D12 — Tue, 12 Nov 2024 07:00:22 GMT

Hi Adam00

To create a calculated column that counts the number of qualifying downline sales execs for each manager, you can use DAX to filter the data based on the manager name and sales target achievement. Here’s how to approach it:

Assuming:

SalesPerson column: Lists all salespeople (both managers and execs).
Manager column: Lists each salesperson's manager.
TargetAchieved column: Contains 1 if the salesperson hit the target, 0 otherwise.

DAX Calculated Column

Here’s the DAX code for the calculated column, which will go in the same table as the salespeople:

QualifiedDownlineCount = VAR CurrentManager = Table[SalesPerson] RETURN CALCULATE( COUNTROWS(Table), Table[Manager] = CurrentManager, Table[TargetAchieved] = 1 )

This calculated column will give each manager the count of downline sales execs who have hit their target. Execs without downlines (i.e., who are not managers) will have a 0 in this column.

Additional Notes

Make sure to replace Table with the actual name of your table.
This calculated column will dynamically update if there are changes in the data.

This approach should give you the number of downline salespeople who qualify for the incentive for each manager.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

Re: Count the number of downline who meets certain criteria

Adam00 — Tue, 12 Nov 2024 07:43:53 GMT

Thanks for the reply. I have tried it and it doesn't seem to work. I will show a screenshot of something similar to what I am looking for. The new column is where I want the number to appear for each staff member.

Re: Count the number of downline who meets certain criteria

Adam00 — Tue, 12 Nov 2024 07:45:12 GMT

Re: Count the number of downline who meets certain criteria

Anonymous — Tue, 12 Nov 2024 08:02:10 GMT

Hi Adam00 ,
Hope this helps you
Suppose we had a data like this

Salesperson Manager Target Achieved (1/0) Qualified Downline

Alice	Alice	1	1
Bob	Alice	0	0
Charlie	Alice	1	1
David	Bob	0	0
Eve	Bob	1	1
Frank	Charlie	1	1
Grace	Charlie	0	0
Heidi	David	1	1
Ivan	David	0	0
Jack	Frank	1	1

we would like to ge the no of people achecived for each manager
then we use dax code :
Qualifying Downline Summary =
SUMMARIZE(
'Sales Data',
'Sales Data'[Manager],
"Qualifying Downline Count",
CALCULATE(
COUNTROWS('Sales Data'),
'Sales Data'[Target Achieved (1/0)] = 1
)
)

or in power query

In the Group By section, select Manager (this will group the data by each manager).
Under New column name, type something like Qualifying Downline Count.
Under Operation, select Sum.
In the Column field, select the newly created Qualified Downline column.

then you will get the output as :

Manager Qualifying Downline Count

Alice

Bob

Charlie

David

Frank

Re: Count the number of downline who meets certain criteria

Adam00 — Tue, 12 Nov 2024 09:14:59 GMT

Seems like I have an error. It says that "the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". I haven't tried the power query method yet tho.

Re: Count the number of downline who meets certain criteria

Anonymous — Tue, 12 Nov 2024 09:22:33 GMT

Hi Adam00 ,
Can you please try in this way it will works

Re: Count the number of downline who meets certain criteria

Adam00 — Wed, 13 Nov 2024 07:22:59 GMT

Thanks for suggestion. It works only to a certain extent as in your table, grace, Heidi, Ivan and Jack are all not managers, so they should not have a count in the final column. Only the count of the managers subordinates who have hit the target should be counted in the list. If the employee is a normal employee, his line should be 0 at the end.

Re: Count the number of downline who meets certain criteria

Anonymous — Thu, 14 Nov 2024 07:36:05 GMT

Hi Adam00 ,

Based on the description, try using the following DAX formula.

DownlineAchievedTarget = VAR ManagerName = [manager name] RETURN CALCULATE( COUNTROWS('Table'), 'Table'[manager name] = ManagerName, 'Table'[target achieved] = 1 )

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Count the number of downline who meets certain criteria

Adam00 — Thu, 14 Nov 2024 08:58:27 GMT

Thanks for the reply. But in this case, the numbers do not go to the manager. Eg. David being the manager of Andrew and Mary should receive 2 instead of 0. Likewise, Andrew and Mary both should be 0 as they don't have down lines.

Re: Count the number of downline who meets certain criteria

johnt75 — Thu, 14 Nov 2024 11:23:46 GMT

Try

Qualified Downline = IF ( 'Table'[Sales Person] = 'Table'[Manager], CALCULATE ( SUM ( 'Table'[Target Achieved] ), ALLEXCEPT ( 'Table', 'Table'[Manager] ) ) )

Note that this will include the manager themselves if they have met their target.

Re: Count the number of downline who meets certain criteria

Anonymous — Fri, 15 Nov 2024 01:00:48 GMT

Hi Adam00 ,

Based on the testing, the method provided above by johnt75 should be helpful.

Please view the DAX formula, if the method helps, please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Wisdom Wu

Re: Count the number of downline who meets certain criteria

Adam00 — Fri, 15 Nov 2024 14:01:15 GMT

Thanks for the reply. I will have to go back and give it a try. I'll mark it as accepted once I've tested it.

Re: Count the number of downline who meets certain criteria

Adam00 — Sun, 17 Nov 2024 10:22:36 GMT

Seems like this worked. But by any chance would u know how to exclude the manager's own count if they themselves have hit the target?

Re: Count the number of downline who meets certain criteria

johnt75 — Mon, 18 Nov 2024 09:31:43 GMT

I think the below should do it

Qualified Downline = VAR CurrentManager = 'Table'[Manager] VAR Result = IF ( 'Table'[Sales Person] = CurrentManager, CALCULATE ( SUM ( 'Table'[Target Achieved] ), ALLEXCEPT ( 'Table', 'Table'[Manager] ), 'Table'[Sales Person] <> CurrentManager ) ) RETURN Result