cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors