- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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] )
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview. ✨
️ November 12th-14th, 2024
Online Event
Register Here
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
10-20-2023 01:11 AM | |||
04-04-2023 04:58 AM | |||
07-06-2024 02:34 AM | |||
05-11-2022 12:26 PM | |||
Anonymous
| 05-10-2023 08:59 PM |
User | Count |
---|---|
21 | |
18 | |
17 | |
7 | |
5 |
User | Count |
---|---|
32 | |
27 | |
19 | |
13 | |
12 |