Count the number of downline who meets certain criteria

[Adam00]

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.

[johnt75 · Accepted solution]

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.

[v-jiewu-msft]

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

[johnt75 · Accepted solution]

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.

[Adam00]

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?

[johnt75 · Accepted solution]

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

[Adam00]

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. 

[v-jiewu-msft]

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.

[Adam00]

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.

[sbezawada]

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

2

Bob

1

Charlie

2

David

1

Frank

2

[Adam00]

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.

[Poojara_D12]

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

[Adam00]

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.

 

 

[Adam00]

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.

 

 

[sbezawada]

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

[Adam00]

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.