Division with calculated measure

[Vaesta]

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

[Anonymous]

@Vaesta

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

[Vaesta]

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 😞 

[Anonymous]

@Vaesta,

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:

[Vaesta]

@Anonymous Actually there are two different tables I get from an Excel doc.

First one has every sale every salesmen made during the week, with lots of other information (like product types and et cetera), and the second one has the goals for both the salesmen and the store for the week)

I've created a relationship between them both to create some views in which everyone can find what they need

[Anonymous]

Hi,

Can you try:

Test = 
IF(Table1[Sales] > RELATED(Table2[Goal1]),"SUPER",
    IF(Table1[Sales] >= RELATED(Table2[Goal2]),"GOAL",
        IF(Table1[Sales] >= RELATED(Table2[Goal3]),"LESSER GOAL","")))

Let me know if this helps.

[Vaesta]

@Anonymous sorry for the late reply, it seems that it didn't work for me, because after trying your Test formula it gave me all blanks as the result

Also, I'm not sure if it'll help, but I have an active filter for the week number (Example, last week was week #41 of this Year. Every week there'll be three new goals for every person to Achieve (They change based on your store and your sales group (For Example: Beira Mar and Centro are different stores, and they'll have different goals, but, Group 1 from Beiramar will have a different goal from Group 2, or Group Novas), the lesser goal is always 85% of the middle goal, and the super goal is always 125% of the middle goal).

It sounds kinda confusing, but in long story short we have 9 stores, with 4 different groups of salesmen in each. Every store and every group will have three different goals they need to achieve every week. In my powerBI view I made I have 1 excel sheet with every product sold by everyone on that week (and all other weeks of the year too), and the other sheet has all the week numbers and the goals for every group in every week. What I need is a formula that will give me which goal every person achieved that specific week (if I change the week filter it'll update with the goals achieved for that week) so I know exactly how much I should be paying them in sales commission

Thank you so much for your help so far.