Target vs Actual Values and variance

Question

Hi, I have a list of targets per month. I want to show the actual total vs the target table, and also calculate the variance. Here is an example of my data.

https://data2actionltd-my.sharepoint.com/:x:/g/personal/aimee_laird_data2action_co_uk/EcITVcSSC6dNh9gtbim7_5UBayIfCx0U3OzSR5MZxGZh3w?e=7H6Gjg

I want to be able to visualise and slice by month to show the actual total value vs the target total. I'd then also like to show the variance, whether that is + or - etc.

aimeeclaird · Answer

Hi Kevin,&nbsp;The example target table is on the first sheet of the example I attached here:&nbsp;https://data2actionltd-my.sharepoint.com/:x:/r/personal/aimee_laird_data2action_co_uk/_layouts/15/Doc.aspx?sourcedoc=%7BC45513C2-0B92-4DA7-87D8-2D6E29BBFF95%7D&amp;file=Example%20Data.xlsx&amp;action=default&amp;mobileredirect=true&nbsp;I'll want to show the actual vs the target for each month to date, with a variance line.&nbsp;Hope this clarifies,&nbsp;Many thanks,&nbsp;

anonymous · Answer

- First create a date table with date and month at least.- Create a contract Table with unique values&nbsp;- Then use this relation between tables :&nbsp;- Create your table using contract from contract table and month from date table&nbsp;-&nbsp;% vs target = DIVIDE(SUM(Data[Value Sold]),SUM(Targets[Target]),0)-&nbsp;Variance = CALCULATE(SUM(Data[Value Sold])-SUM(Targets[Target]))&nbsp;&nbsp;

anonymous · Answer

Hi,&nbsp;Could you precise the strucure of the target table please? it is misssing.&nbsp;Also you want to visualise it in a classic table?&nbsp;&nbsp;

anonymous · Answer

Okay, is this okay&nbsp; :&nbsp;&nbsp;Hope it is what you expect.

aimeeclaird · Answer

Hi Kevin&nbsp;That looks like it would be a sensible output. Please can you share your pbix. file or calculations?

Forum Discussion

Target vs Actual Values and variance

7 Replies

Related content

Budget, actual and variance matrix

Calculate variance between actual and target On or Before a target date

Bar Chart Showing Variance – Target Sales vs Net Sales

Variance bar chart

Stacked bar charts with variance