Bonus pool of sales of managers with their Salesman (metric with filtered sum + another sum from th)

[Luizcarlospf]

Hello, I have a question about whether it is possible or not to solve this problem.

I have a sales portfolio that contains salespeople and sales managers.

However, managers do not receive bonuses on sales.

The sales bonus for managers is shared between their subordinate salesman. As explained in the job hierarchy in the attached image.

My question is. Is it possible in just one metrics to add the salespeople bonus + the managers bonus / number of subordinate salespeople?

[v-jingzhang · Accepted solution]

Hi @Luizcarlospf 

You can create below measure and put it in a table visual to check the result.

Bonus = 
IF (
    SELECTEDVALUE ( 'Table'[SuperiorLevel] ) = "noSuperior",
    0,
    VAR superior = SELECTEDVALUE ( 'Table'[SuperiorLevel] )
    VAR quantity = COUNTROWS ( FILTER ( ALL ( 'Table' ), 'Table'[SuperiorLevel] = superior ) )
    VAR managerBonus =
        SUMX (
            FILTER ( ALL ( 'Table' ), 'Table'[name] = superior ),
            'Table'[BonusPerSales]
        )
    VAR salesmanBonus = DIVIDE ( managerBonus, quantity ) + SUM ( 'Table'[BonusPerSales] )
    RETURN
        salesmanBonus
)

Kindly let me know if this helps.
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[Luizcarlospf]

Perfect!
Thanks

[lbendlin]

one more question - can the bonus be impacted by anything else (meaning it might need to be a measure) or is it ok to do it as a calculated column?

[Luizcarlospf]

The total bonus cannot be static .. it has to depend on other filters.

I thought of a solution, but for that I would have to create another table (only with the names of the salesman) with the bonus of the "managers" diluted in each salesman.

But I really need a solution in a metric, and that metric has to be influenced by the filters

[lbendlin]

yes, that is possible. Please provide sample data in usable format (not as a picture).

[Luizcarlospf]

I can't attach the .pbi file to the answer 

I used this data set as an example:

name;level;value$;SuperiorLevel;%BonusPerSales;BonusPerSales
Pedro;selesman;10;Jason;0.1;1
Joao;selesman;10;Jason;0.1;1
Lucas;selesman;10;Fernando;0.1;1
Mateus;selesman;10;Fernando;0.1;1
Fernando;manager;60;noSuperior;0.1;6
Jason;manager;100;noSuperior;0.1;10