Forum Discussion
Calculate target with changing target value.
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 'campaign target' table. Under normal circumstances I can simply calculate their target as number of days * their target. For a week that'd be 5*5.
Once I've established the target I read in the call data and get the number of 'SALE' records and compare the two to get the % to target.
Where it gets tricky is there is a table that contains adjustments to that target value:
Username | Adjusted Target | Date |
| ooppa | 3 | 15/03/2021 00:00 |
| uunder | 1 | 16/03/2021 00:00 |
| uunder | 0 | 18/03/2021 00:00 |
| aadams | 2 | 19/03/2021 00:00 |
| rroger | 0 | 20/03/2021 00:00 |
| llima | 3 | 22/03/2021 00:00 |
| rroger | 1 | 23/03/2021 00:00 |
| ppage | 1 | 24/03/2021 00:00 |
what I need to do is to include that revised value in the target calculation. One user may have their target changed daily for a wekk, others won't have theirs changed for a month or more. There's no pattern to this.
As an example : if I were to run the report for "rroger" for - 19/03 to 21/03 - I would need to calculate the original target of 5*2 days, plus one day at the revised target of 0 - for a total target of 10.
If I were to run the report for "rroger" for 19/03 to 24/03 - I would need 4 days at the usual target of 5, plus one day at the revised target of 0 and another at the revised target of 1, total target of 21, instead of 30.
I can currently calculate the basic target as a measure
but I absolutely cannot get my head around what I would need to add to it to include that adjustment data in the calculation.
Hopefully the Example Data is clear
The 'Call Data' is 15K sample rows of calls with some sales in.
Campaign Target is the basic target values
Target Adjustment has usernames, dates and the adjusted target value
Users is a flat list of expected users.
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.
2 Replies
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.
