Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
nbs33
Helper II
Helper II

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 measure to do this?

 

EDIT: 

The end goal is something like the visualization below. Each of the colored lines represents the total value of for sales reps that land in a 5% increment bucket. For example, red would be sum of all sales reps who's total QTD revenue is between 0%-5% above the goal, orange bewteen 5% to 10%, etc. 

 

nbs33_1-1645724664867.png

 

4 REPLIES 4
ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

@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))




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

@nbs33 ,

Measures like (common date table, common sales rep table )

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

QTD Goal= CALCULATE(SUM(Sales[Goal]),DATESQTD(('Date'[Date])))

 

Achieve % = divide([QTD Sales] -[QTD Goal] ,[QTD Goal])

 

countx(filter(values(salesrep[salesrep]), [Achieve %] >.05) , [Salesrep] )

 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors