Forum Discussion

Aimeeclaird's avatar
Aimeeclaird
Icon for Helper IV rankHelper IV
5 years ago

Target vs Actual Values and variance

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.c...
Need Help
Show and Tell
Tutorial Requests
Aimeeclaird's avatar
Aimeeclaird
Icon for Helper IV rankHelper IV
5 years ago

Hi Kevin

 

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

- First create a date table with date and month at least.

- Create a contract Table with unique values

 

- Then use this relation between tables :

 

- Create your table using contract from contract table and month from date table

 

- % vs target = DIVIDE(SUM(Data[Value Sold]),SUM(Targets[Target]),0)

- Variance = CALCULATE(SUM(Data[Value Sold])-SUM(Targets[Target]))

 

 

  • Aimeeclaird's avatar
    Aimeeclaird
    Icon for Helper IV rankHelper IV
    5 years ago

    Hi, thanks for your response and suggestion.

     

    I'm not sure that this solution will work for us though. How are you looking up the target month? As the target changes for each month?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi, 

       

      I do not get it. THe calculation I did takes in consideration the target changing each month.. Could you be more precise please?