Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
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/
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))
Proud to be a 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] )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
20 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
27 | |
23 | |
22 | |
22 |