reward system based on profit

[Katerina_SL]

Hello everyone!

I would like to ask you to help me with reward system in company.

I have table in power bi with employees and their profit.

I would like to create the reward system for them like on some competition. Each employee will get point every month based on profit.

For example, in September we have 10 employees with profit. In this case only half employees with get points. The employee with highest profit will get 5 point, others on 1 less and other half of employees with the lowest profit will get 0 points.

If in October we will have 14 employees. The employee with highest profit will get 7 point.

Also points have to be summarized in case I will check quarters and years points for each employee.

Thank you.

[zenisekd · Accepted solution]

You could either do this in power query or with dax. Create a new table, which would summarize employee profit per month, quarter and year and then via conditional column add the points. Thanks to this, you could easily visualize, filter, slice, sum the points.

Kudos and mark as solution appreciated.

[zenisekd · Accepted solution]

You could either do this in power query or with dax. Create a new table, which would summarize employee profit per month, quarter and year and then via conditional column add the points. Thanks to this, you could easily visualize, filter, slice, sum the points.

Kudos and mark as solution appreciated.

[Katerina_SL]

I did it. Thank you very much for idea.

[Katerina_SL]

Thank you for quick respond.

I've created table with columns:

employee, year, quarter, month and total profit.

But I don't understand, how I can create conditional column. Because if I would like to do it in transform data field, there is no this calcualted (via dax) table.

And also could you explain me please how I can use the condition that each month the max number of points depends on number of employees. For example, if there are 10 employees in september, the max number of points will be 5, but if we have 20 employees in october, the max number of points will be 10.

Thank you.

[Katerina_SL]

Yes, I did it via DAX as you recommeded. Thank you.

[zenisekd · Accepted solution]

I am not qite sure I follow your question. You have created a new table - where in Desktop or Power Query? In Power Query you create new conditional column. In Desktop you have to use new calculated column for this table.  

Although I am not sure, how do you work with points, if you have odd number of employees... ? 🙂

Since your evaluation has to be dynamic, it will be probably easier to calculate this column in Dax. What you could do is probably something like create a descending rank by profit over month - 1/2 count and then instead of negative number give 0.