Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
Wondering if someone may be able to assist. I need to somehow create a measure that can provide me with the desired % of Target readings based on some rules as set out in the example below.
Raw data looks something like this - where a row represents an individual sales record in the given month (Note: I have created a Calculated Column called Percentage of Target already that gives the column on the right):
State | Salesperson | Quota | Closed | Percentage of Target |
QLD | Joe Bloggs | $ 1.00 | $ 799.00 | 79900% |
QLD | Joe Bloggs | $ 1.00 | $ 799.00 | 79900% |
QLD | Joe Bloggs | $ 1.00 | $ 399.00 | 39900% |
NSW | Jane Doe | $ 2,500.00 | $ 1,400.00 | 56% |
NSW | Jane Doe | $ 2,500.00 | $ 2,000.00 | 80% |
WA | Sarah Lee | $ 5,000.00 | $ 8,000.00 | 160% |
VIC | Jeff Smith | $ 10,000.00 | $ 9,000.00 | 90% |
VIC | Jeff Smith | $ 10,000.00 | $ 6,000.00 | 60% |
VIC | Jeff Smith | $ 10,000.00 | $ 3,000.00 | 30% |
Below is a view of what a SUM of the "Percentage of Target" calculated column provides versus what I really need to appear:
Current Percentage of Target | Desired % of Target | |
Joe Bloggs | 199700% | 100% |
Jane Doe | 136% | 100% |
Jeff Smith | 180% | 180% |
Sarah Lee | 160% | 100% |
The quotas and the rules are as follows:
Normal Quota | |||
Joe Blogs | $ 10,000.00 | In his first Quarter ($1 Quota) | |
Jane Doe | $ 10,000.00 | In her 4th Month (25% of Normal Quota) | |
Sarah Lee | $ 10,000.00 | In her 5th or 6th Month (50% of Normal Quota) | |
Jeff Smith | $ 10,000.00 | After 6th month (Normal Quota) |
Main rule: If a sales person is in a ramp up period (i.e. prior to the 7th month) if they exceed 100% of their target - it should appear as 100% instead of the actual percentage achieved against the reduced ramp up target.
Any ideas on how best to achieve this result that I could display in a dashboard?
You can use the DAX MIN() function to cap the percentage like so
result = MIN(achieved percentage, 1)
can never be higher than 100 %
Thanks @lbendlin ,
The only issue with that is if the sales person is not on ramp up - the % achieved can be over 100% and we need to show that.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
20 | |
18 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |