Question on Calculating variance from Target against actual

[train]

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.
   My pbix link is https://drive.google.com/file/d/19UqWP2xwWK5eB4S2CUngc5abiWCHHIDG/view?usp=sharing

[Melissa]

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.

Here’s your sample file.
Target Test.pbix (81.9 KB)

I hope this is helpful

[train]

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.

[Melissa]

Hi @train,

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

What Are "Junk Dimensions" And Why They're Anything But Junk - Power BI Data Modeling Pro Technique

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

I hope this is helpful.

[train]

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.

[Melissa]

@train

Kind of utilized that junk dimension approach as it becomes painful to handle 5+ dimensions.

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…

@train

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.

If I understand correctly, try a right click on the Values section of the table field and select “Show items with no data”

I hope this is helpful

[EnterpriseDNA]

It’s great to know that you are making progress with your query @train.

Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’.

[EnterpriseDNA]

Hi @train, due to inactivity, a response on this post has been tagged as “Solution”. If you have any concern related to this topic, you can create a new thread.