Forum Discussion

nbs33's avatar
nbs33
Icon for Helper II rankHelper II
4 years ago

Sum if Sales Rep Above Goal

Hi, I have a ordes table, a table for sales rep goal by quarter and a date table. I would like to group sales reps by percentage of revenue QTD above their goal in 5% increments. How would I write a ...
Need Help
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity Champion
4 years ago

Hi,

You can use MROUND to achieve this: 

if( Sales[Gross profit rate]>0, MROUND(Sales[Gross profit rate],0.05), MROUND(Sales[Gross profit rate],-0.05))),0)

Thiss will round the measure (in mu example gross profit) to 5% increments. You can use your QTD measure as a base measure and then just compare this QTD measure to a QTD target measure (all within the MROUND)

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

  • nbs33's avatar
    nbs33
    Icon for Helper II rankHelper II
    4 years ago

    Hi ValtteriN thanks for the reply.  When I try to use your DAX there seems to be syntax error. Is there an additional IF statment needed?

    • ValtteriN's avatar
      ValtteriN
      Icon for Community Champion rankCommunity Champion
      4 years ago

      nbs33  

      Sorry about that, I used dax from another solution I had. Yes the example requires additional IF. 

      Here is modified DAX: 

      if( Sales[Gross profit rate]>0, MROUND(Sales[Gross profit rate],0.05), MROUND(Sales[Gross profit rate],-0.05))