Forum Discussion

Vaesta's avatar
Vaesta
Frequent Visitor
7 years ago

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 ...
Need Help
Vaesta's avatar
Vaesta
Frequent Visitor
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Vaesta
    Frequent Visitor
    7 years ago

    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.