cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Helper II

## reward system based on profit

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.

2 ACCEPTED SOLUTIONS
Super User

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.

Super User

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.

5 REPLIES 5
Super User

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.

Helper II

I did it. Thank you very much for idea.

Helper II

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.

Helper II

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

Super User

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.