powerbidaxpowerqueryaveragepowerbi-desktop

Group average as a baseline & individual variance to baseline

Description of issue: I am trying to get a table to show the average of select individual's averages, where the individuals are in a specific group. That group is controlled by a slicer or filter. AND.. there is another slicer on Year-Month so the averages need to be only for that month.

The issue is that for some reason, all my attempts break past the slicer's controls and show ALL employees.... (Each half of the screen is independently controlled through the slicers at the top. Interactions have been disabled between the two sides.)

Link to example file pbix: https://wetransfer.com/downloads/ddc07be35cdda911f10f4e333cf1c3fb20250128024409/a5c695?t_exp=1738637049&t_lsid=c0c3d584-70ed-40e0-81a5-dce82868ec6f&t_network=link&t_rid=YXV0aDB8Njc5N2MzZmQxZDU3OWIyMDZmNTY1NGE4&t_s=download_link&t_ts=1738032249

PowerBI issue - Current Situation

Thought process of the requirement 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 average of their peer's averages in their respective CostCenters)" .

Data Model Overview:

Fact Table: Sales Fact - Contains generic sales records.

Historical Employee Table - Tracks employee movements between regions (with StartDate, EndDate, and Region fields).

Cost Center Region Table - Maps regions to their respective cost centers.

I handle "region assignments at the time of sale" using this calculated column in the Sales Fact table:

    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

This field is then used in a relationship to the Cost Center Region Table.

I am pulling my hair out! Here is one attempt. I've tried other combinations of REMOVEFILTERS as well

CostCenter Avg = 
AVERAGEX(
    ALL('fact_Sales'[Employee]), 
    CALCULATE(AVERAGE('fact_Sales'[SalePrice]))
)
Solution

  • If a measure returns a result, it will always be displayed regardless of relationships & filters. This is what is happening here.

    Update your measure to:

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

    And with the above, your other measures could look like:

    Var to Avg = [AvgSales] - [CostCenter Avg]

Var to Avg % = DIVIDE([Var to Avg], [CostCenter Avg])

    Result