Forum Discussion
Division with calculated measure
Hello there,
I have a question that I haven't found an answer in the forums yet.
I'm trying to create a view for my Personal Department to use so they know exactly how much they have to pay our employees w/o having to waste too much time calculating.
Thing is, the salesmen have three goals to achieve, and they'll get paid morre according to the goals they achieve every week.
I've already made the whole view, except for the payment part, in which I was trying to use a calculated measure to give me the following:
Goal 1 is the biggest goal, which rewards you with 3,5% of the total amount they sold on the week,
Goal 2 is the middle goal, and rewards you with 2,5% of the total amount.
Goal 3 is the smallest one, and rewards you with 1,5% of the total amount.
Thing is, when I try to add a measure with an If statement it doesn't work out. My statement was written like this:
Goal = IF(WeeklySale >= Goal1, "SUPER", IF(WeeklySale >= Goal2, "GOAL", IF(WeeklySale >= Goal3, "LesserGoal","")))
The thing is, as I work with an multi-franchise group, we have different goals for different stores, and when I try to use that specific string above, it calculates the total of all salesmen, instead of testing for each row.
Is there a way I can get the IF function to calculate the goal achieved for each salesmen on their row, instead of trying to give me the total amount?
I've also added this image as below, to clarify things
1 is my IF formula as it follows, telling me everyone achieved their "Super" goal (The highest one), alongside with the amount that person sold during that week
2 is the smallest goal
3 is the middle goal
4 is the highest goal
If you take for example the first person, "Aline Brandao" she sold R$10.691, the smallest goal was R$11.000, and after my IF function it says she achieved SUPER goal, because the IF function is adding every sales amount, and every Super Goal, whereas I want it to test results for each row separately
6 Replies
I think you should use calculated column instead of using measure for this case. Let change your measure to calculated column with similar forumal. Please let me know if this works.
Duc
Thank you for your help, unfortunately adding a Column with the IF statement still gives me the same result, showing everyone as if they've reached the "SUPER" goal, because it is still calculating the total amount sold / Total amount of Super Goal, instead of testing it row by row :(
Are all those goals column and sales in the same table? I create a sample data like yours and the formula work for me like below:
