Forum Discussion

MrPatrick's avatar
MrPatrick
Helper I
5 years ago
Solved

Calculate target with changing target value.

Example Data   I've got a database for a call centre, I've been asked to produce a report that includes a 'distance to target' value for sales. The target is 5 daily, this value is stored in a 'ca...
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    Hi MrPatrick ,

     

    Create a calendar table and related with your Call data then add the following measure:

     

    SimpleTarget = 
VAR temp_table =
    ADDCOLUMNS (
        SUMMARIZE (
            'Call Data',
            'calendar'[Date],
            Users[User],
            'Call Data'[Campign_id]
        ),
        "sum", [totalSales] + 0,
        "adjusted",
            CALCULATE (
                MAX ( 'Target Adjustment'[Target] ),
                FILTER (
                    'Target Adjustment',
                    'Target Adjustment'[Campaign_id] = 'Call Data'[Campign_id]
                        && 'Target Adjustment'[User] = Users[User]
                        && 'Target Adjustment'[Date] = 'calendar'[Date]
                )
            ),
        "target",
            CALCULATE (
                SUM ( 'Campaign Target'[Target] ),
                FILTER (
                    'Campaign Target',
                    'Campaign Target'[Campaign_id] = 'Call Data'[Campign_id]
                )
            )
    )
VAR Result =
    ADDCOLUMNS ( temp_table, "Result", COALESCE ( [adjusted], [target] ) )
RETURN
    SUMX ( Result, [Result] )

     

    Results below and in attach PBIX file:

    Has you can see rroger and aadams have changes in target so value instead of 15 is giving 12 and 10.

     

MFelix's avatar
MFelix
Super User
5 years ago

Hi MrPatrick ,

 

Create a calendar table and related with your Call data then add the following measure:

 

SimpleTarget = 
VAR temp_table =
    ADDCOLUMNS (
        SUMMARIZE (
            'Call Data',
            'calendar'[Date],
            Users[User],
            'Call Data'[Campign_id]
        ),
        "sum", [totalSales] + 0,
        "adjusted",
            CALCULATE (
                MAX ( 'Target Adjustment'[Target] ),
                FILTER (
                    'Target Adjustment',
                    'Target Adjustment'[Campaign_id] = 'Call Data'[Campign_id]
                        && 'Target Adjustment'[User] = Users[User]
                        && 'Target Adjustment'[Date] = 'calendar'[Date]
                )
            ),
        "target",
            CALCULATE (
                SUM ( 'Campaign Target'[Target] ),
                FILTER (
                    'Campaign Target',
                    'Campaign Target'[Campaign_id] = 'Call Data'[Campign_id]
                )
            )
    )
VAR Result =
    ADDCOLUMNS ( temp_table, "Result", COALESCE ( [adjusted], [target] ) )
RETURN
    SUMX ( Result, [Result] )

 

Results below and in attach PBIX file:

Has you can see rroger and aadams have changes in target so value instead of 15 is giving 12 and 10.

 

MrPatrick's avatar
MrPatrick
Helper I
5 years ago

Hi MFelix, just wanted to say that I've just got this integrated into my live data and it's working perfectly. 

 

Can't thank you enough, almost certainly wouldn't have got this working in the time scale I needed without your reply.