Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
Perfect!
Thanks
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |