Struggling with breaking context in doing 'variance to group average'

[dant_]

Hello all,

I am running into one of those scenarios where it is unexpectedly difficult to accomplish something that can be done in Excel in 2 seconds (because of Excel's ability to use free/arbitrary cell references).

I am providing the following:
     • Description of the issue

     • Example_File.pbix ........  https://www.dropbox.com/scl/fi/zemp5u8g23zluqwblu8u0/Example_File.pbix?rlkey=ox5dsknvfsqbhjroiitn1lp...
     • Current Dax formulas

     • Screenshots of the Relationships, Tables, AND example of expected outcome

Description:
The example file is derived to mimic my true file which I cannot share due to privacy. 
In the file, my fact table is Sales Fact containing generic sales records. Then I have a historical employee (sales associate) dim table. This table contains record start & end dates. It also contains a field called [Region]
The reason this table is historical is to track movement of an employee being associated with a region one month but then they were transferred to another region. The KPI's need to align with that movement.

Now, to add a layer  - These regions are contained by 2 different [Cost Centers]. So, there is a Cost Center Region dim table. 

So the ultimate requirement or goal is to show separate tables that are controlled by a Date (Month) Slicer and a CostCenter slicer(or filter). These tables are to depict the [Employee] as rows, and the following values as columns:
     • Employee's [Average Sales$]

     • the [Average Sales$] of the [Employee's] associated with the selected [Cost Center].... Lets call this [CostCenter Avg]

     • Absolute Variance to [CostCenter Avg]

     • Relative Variance (%) to [CostCenter Avg]

I am struggling with the 3 fields in red. This [CostCenter Avg] needs to adhere to the [Month] slicer. While at the same time also only adhering to the [Employees] in the [CostCenter] selected by slicer or filter. So I think the reason I am struggling is that its kind of breaking context in a strange way.

Seen in the below, In the lower two tables I have everything up until the point of the 3 red fields above.

Here is an example of what I am trying to achive, which I typed in excel then pasted on to a screenshot (indicated in red boxes)

Also I do not want to use a weighted average, so I know it is technically an "average of an average". But that doesn't matter from the mgmt perspective. Thought process being; "Regardless of volume, here are my employees, here are their averages, and here is how they are doing in comparison to the baseline (The avearge of their peer's averages in their respective CostCenters)" 

Another thing that adds complexity is I need to associate values to the correct [Region] when an [Employee] moves from one [Region] to another. I stuggled with this too but ultimately ended up adding a calculated column on the Fact table with the following formula: 

x_RegionAtTimeOfSale = 
VAR SaleDate = 'fact_Sales'[SaleDate] 
VAR EmployeeID = fact_Sales[EmployeeID]
VAR CurrentRegion = 
CALCULATE (
MAX ( 'dim_Employee(Historical)'[Region] ),
'dim_Employee(Historical)'[EmployeeID]= EmployeeID,
SaleDate >= 'dim_Employee(Historical)'[StartDate] &&
SaleDate <= 'dim_Employee(Historical)'[EndDate]
)
RETURN CurrentRegion

And subsequently, this [x_RegionAtTimeOfSale] is used in the relationship to obtain CostCenter from the 'Cost Center Region dim table'

Here are what the tables look like:

I am pulling my hair out trying different combinations of ALLEXCEPT() and REMOVEFILTERS() but cannot land on the correct one.
Thank you to anyone who can provide any input or guidance! 

[dant_ · Accepted solution]

Hi Leroy Lu - thanks but I have been helped and have a solution I am providing below for anyone else who comes across a similar issue and can use this info:

CostCenter Avg = 
  var result =
    AVERAGEX(
      ALL('fact_Sales'[Employee]), 
      [AvgSales]
    )
  return IF(COUNTROWS(fact_Sales) > 0, result)

[Anonymous]

Hi, @dant_ 
 

We are absolutely delighted to hear that the issue has been resolved. If you’re inclined, you might consider accepting this solution, as it could also assist other community members facing the same problem, enabling them to find a resolution more swiftly.

Of course, if there is anything else we can do for you, please do not hesitate to contact us.

Looking forward to your reply.

Best Regards,

Leroy Lu

[Anonymous]

Hi, @dant_ 
Thank you for your detailed description. I have a certain understanding of your needs, but unfortunately, I am unable to open the sample data you shared.

We have some ideas for your case, but to better test them, we suggest you re-upload your data using GitHub. During the upload process, please ensure that no sensitive information is included. If you can also share an Excel file with the expected results, that would be even better, as it will help us more accurately determine if our understanding aligns with your needs.

Looking forward to your response, and thank you for your cooperation!

 
Of course, if you have any new discoveries or questions, please feel free to get in touch with us.
 

Best Regards,

Leroy Lu