cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Luizcarlospf
Helper I
Helper I

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

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.

Luizcarlospf_0-1615006000901.png

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.

Luizcarlospf_1-1615006008371.png

 

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

Luizcarlospf_2-1615006340882.png

 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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
)

031001.jpg

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.

View solution in original post

6 REPLIES 6
v-jingzhang
Community Support
Community Support

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
)

031001.jpg

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.

View solution in original post

Perfect!
Thanks

lbendlin
Super User III
Super User III

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?

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
Super User III
Super User III

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

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

 

Luizcarlospf_1-1615075592481.png

 

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

html:
Carousel MBAS ARun Part 1 768x460

MBAS 2021

Join Arun Ulagaratchagan and the Power BI team at MBAS! #MSBizAppsSummit #CommunityRocks

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

Top Kudoed Authors