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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.