Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a dataset generaed from Dynamics365 with rows comprising unique order refs (Ref), each ref has an Account Manager (user) Assigned, along with a date (can be more than one ref per date) and the Monthly Target for each Account Manager, from the monthly target Dynamics calculates 20%, 40%, 60%, 80% and 120% of the target value to use as range values for a gauge visualisation.
The aim of the gauge is to show performance for each manager over a period of time against margin (collumn not shown)
If I create the gauge and use average of target and filter to a single Account Manager and realative date of in the last calendar month it works. My problem is showing the gauge with more than one account manager or multiple months as the margin value increases correctly, but the traget values remain the same.
So what I think I need is a way to only assign the traget values to the first ref of each month for each account manager, or a measure to work out the ratio of the total monthly figure of the monthly target of refs for each manager per month?
Example of the data (more collumns but not needed for this problem)
REf | Account manager | Date | Monthly Target | 20% | 40% | 60% | 80% | 120% |
CP-28257-H9E1 | AM5 | 01/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28196-X2Z7 | AM1 | 01/10/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-28119-T3T0 | AM6 | 01/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28098-J5A4 | AM6 | 01/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28312-M1O1 | AM5 | 04/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28300-Q1V9 | AM6 | 04/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28260-Y1Q8 | AM5 | 04/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28386-Y0V8 | AM3 | 13/10/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-28377-H2K1 | AM5 | 13/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28279-E2O1 | AM6 | 25/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28270-H9M8 | AM6 | 25/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28416-X8S0 | AM6 | 26/10/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28446-P0J0 | AM4 | 02/11/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28412-F4V0 | AM3 | 02/11/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-28553-S7U4 | AM4 | 03/11/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28551-Y3E3 | AM3 | 03/11/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-28480-J2T7 | AM6 | 29/11/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28443-Y1O7 | AM6 | 29/11/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28403-F0E2-Q | AM3 | 29/11/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-01065-R9Q7 | AM3 | 29/11/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-01064-Y6X1 | AM3 | 29/11/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-01033-D4Y2 | AM3 | 29/11/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-90033-E5E8 | AM1 | 30/11/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-01034-L5L2 | AM3 | 30/11/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-90034-Q8P3 | AM1 | 01/12/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-28695-A5H6 | AM1 | 01/12/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-28694-L6K8 | AM1 | 01/12/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-28671-G7I9 | AM4 | 01/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28619-F9L2 | AM3 | 01/12/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-28485-N0I2 | AM5 | 01/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-01066-Q9L3 | AM3 | 01/12/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-28717-T7A2 | AM1 | 16/12/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-28716-T1K0 | AM1 | 16/12/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-28666-H3L9 | AM6 | 16/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28656-T6W2 | AM6 | 16/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-01211-R0D7 | AM6 | 16/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-01181-S2T6 | AM3 | 16/12/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-01135-J5H7 | AM3 | 16/12/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-01088-B2V5 | AM3 | 17/12/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-01053-P8T3 | AM6 | 17/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-01042-W5M3 | AM3 | 17/12/2021 | 30000 | 6000 | 12000 | 18000 | 24000 | 36000 |
CP-01026-Q1H2 | AM1 | 17/12/2021 | 0 | 0 | 0 | 0 | 0 | 0 |
CP-01078-Z2V9 | AM6 | 21/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-01077-V4X8 | AM6 | 21/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
CP-28506-M5T3 | AM5 | 29/12/2021 | 40000 | 8000 | 16000 | 24000 | 32000 | 48000 |
Examples of the gauge visualisation to follow..
Any assistance would be greatly appreciated
Thank you
Hi @jamiegmonkey ,
Here are the steps you can follow:
1. Create calculated column.
combination =
'Table'[REf]&""&'Table'[Account manager]
rank =
RANKX(FILTER(ALL('Table'),MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),[combination],,ASC)
Column =
IF(
'Table'[rank]=1,'Table'[Monthly Target],0)
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Here are the gauges with the issue
Gauge for last calendar month with all Account Mangers, note range values don't change but the Margin Value does
For a single Account Manager over last calendar month - this is correct
Single Account Manger but over last 2 months - Value changes but the start values for the gauge don't as the average hasn't changed
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |