# Question on Calculating variance from Target against actual

Hello Experts

Have a question on how to model and do the calculations in this scenario.

I have a table with the Sales transactions by Team.

Second table have the targets by team for the Fiscal Year. Fiscal year starts 05/01

## FY Team Target Qty Group Customer

FY22 Team A 2000 GRP1 100
FY22 Team A 3500 GRP2 101

Filter, i have is for the Fiscal Year & Month. For example for my Nov 2021 filter my expected result of report is

I have tried creating the Key in both target table and the Sales Table to link up the target info. But i am not sure how i can link the Calendar to the Target as my target is not getting picked up in the report.

2 Issues or questions i need guidance on

1. How can i pick the correct Target based on my Selected Fiscal Year & Month.
2. How can i calculate the variance as target is almost like the dimension and cannot be subtracted from the column.

Hi @train,

Separate dimensions from your facts so you can built a proper model (based on your sample I couldnāt determine if āgroupā is meant to be an attribute of Team or Customer so I separated that as wellā¦)

Thereās no Relationship between the Calendar and Targets table because they donāt share the same granularity. Inside CALCULATE you can create that virtually using TREATAS for example.

Marked your Calendar table as a Dates table.
Created some simple measures

``````Sales = SUM('Fact'[Qty] )

Sales MTD =
CALCULATE(
[Sales],
DATESMTD( 'Calendar'[Date] )
)

Sales YTD =
CALCULATE(
[Sales],
DATESYTD( 'Calendar'[Date], "04/30")
)

Sales PY =
CALCULATE(
[Sales YTD],
PREVIOUSYEAR('Calendar'[Date], "04/30" )
)

Target CY =
CALCULATE(
SUM('Sales Targets'[Target Qty] ),
TREATAS( VALUES('Sales Targets'[FY] ), 'Calendar'[FiscalYear] )
)

Variance = [Sales PY] - [Target CY]
``````

Depicted above your data, below the result.

Target Test.pbix (81.9 KB)

Thanks @Melissa for the guidance.

I was trying to avoid that separation of dimensions. The reason was the targets are set for a combination of 5 different attributes. In my sample model i had only 2 ( Teams & Groups). But, I am thinking it make sense to separate this as suggested or is there a way that you have come across to keep the Sales Targets to be treated as 1 table and build key to relate to the actual sales.

Second thing is the result seems off and i couldnāt understand why
if i select the 2021 - Nov, the Sales PY did not seem to calculate. Even though the data for Team A is 320. Also the CY Target also shows wrong as the Team A GRP1 target is showing 2000 whereas the table doesnāt have. Something got messed up.

Hi @train,

You can explore junk dimensions @BrianJ did a video about that, Iāll leave the link here

Nov 2021 dates belong to your FY Nov 2022. Your model doesnāt include data for Nov 2019 so there wonāt be a result for Sales PY when selecting FY Nov 2021. Hope that makes senseā¦

.
Target measure should be:

``````Target CY =
CALCULATE(
SUM('Sales Targets'[Target Qty] ),
TREATAS( VALUES('Calendar'[FiscalYear] ), 'Sales Targets'[FY] )
)
``````

with this result

1 Like

Thank You @Melissa .

Kind of utilized that junk dimension approach as it becomes painful to handle 5+ dimensions. One thing that i noticed was since the report is using the dimension table, if there is no target set for the combination, i miss that line event though there is sales associated. Is there a trick to overcome that. I was thinking of creating a summary based on the Sales Fact and then append to the dimension table with a 0 target.

I messed up on the Fiscal Year question. Apologies on that point.

Really appreciate your help on this.

Okay then you should preferably still separate that from your Facts into its own Dimension table and only keep a Key on the Fact table sideā¦

If I understand correctly, try a right click on the Values section of the table field and select āShow items with no dataā