Forum Discussion

jamiegmonkey's avatar
jamiegmonkey
New Member
4 years ago

Account manager targets

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)

 

REfAccount managerDateMonthly Target20%40%60%80%120%
CP-28257-H9E1AM501/10/202140000800016000240003200048000
CP-28196-X2Z7AM101/10/2021000000
CP-28119-T3T0AM601/10/202140000800016000240003200048000
CP-28098-J5A4AM601/10/202140000800016000240003200048000
CP-28312-M1O1AM504/10/202140000800016000240003200048000
CP-28300-Q1V9AM604/10/202140000800016000240003200048000
CP-28260-Y1Q8AM504/10/202140000800016000240003200048000
CP-28386-Y0V8AM313/10/202130000600012000180002400036000
CP-28377-H2K1AM513/10/202140000800016000240003200048000
CP-28279-E2O1AM625/10/202140000800016000240003200048000
CP-28270-H9M8AM625/10/202140000800016000240003200048000
CP-28416-X8S0AM626/10/202140000800016000240003200048000
CP-28446-P0J0AM402/11/202140000800016000240003200048000
CP-28412-F4V0AM302/11/202130000600012000180002400036000
CP-28553-S7U4AM403/11/202140000800016000240003200048000
CP-28551-Y3E3AM303/11/202130000600012000180002400036000
CP-28480-J2T7AM629/11/202140000800016000240003200048000
CP-28443-Y1O7AM629/11/202140000800016000240003200048000
CP-28403-F0E2-QAM329/11/202130000600012000180002400036000
CP-01065-R9Q7AM329/11/202130000600012000180002400036000
CP-01064-Y6X1AM329/11/202130000600012000180002400036000
CP-01033-D4Y2AM329/11/202130000600012000180002400036000
CP-90033-E5E8AM130/11/2021000000
CP-01034-L5L2AM330/11/202130000600012000180002400036000
CP-90034-Q8P3AM101/12/2021000000
CP-28695-A5H6AM101/12/2021000000
CP-28694-L6K8AM101/12/2021000000
CP-28671-G7I9AM401/12/202140000800016000240003200048000
CP-28619-F9L2AM301/12/202130000600012000180002400036000
CP-28485-N0I2AM501/12/202140000800016000240003200048000
CP-01066-Q9L3AM301/12/202130000600012000180002400036000
CP-28717-T7A2AM116/12/2021000000
CP-28716-T1K0AM116/12/2021000000
CP-28666-H3L9AM616/12/202140000800016000240003200048000
CP-28656-T6W2AM616/12/202140000800016000240003200048000
CP-01211-R0D7AM616/12/202140000800016000240003200048000
CP-01181-S2T6AM316/12/202130000600012000180002400036000
CP-01135-J5H7AM316/12/202130000600012000180002400036000
CP-01088-B2V5AM317/12/202130000600012000180002400036000
CP-01053-P8T3AM617/12/202140000800016000240003200048000
CP-01042-W5M3AM317/12/202130000600012000180002400036000
CP-01026-Q1H2AM117/12/2021000000
CP-01078-Z2V9AM621/12/202140000800016000240003200048000
CP-01077-V4X8AM621/12/202140000800016000240003200048000
CP-28506-M5T3AM529/12/202140000800016000240003200048000

 

Examples of the gauge visualisation to follow..

Any assistance would be greatly appreciated

 

Thank you

 

Need Help

2 Replies

  • jamiegmonkey's avatar
    jamiegmonkey
    New Member
    4 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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