Account manager targets

[jamiegmonkey]

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

[v-yangliu-msft]

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

[jamiegmonkey]

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