Always show a specific column in matrix, regardless of filter

[mikesdunbar]

I'm trying to present some financial data that compares actuals versus different time periods (last month, 2024 budget) for different locations (NYC, LA). My users should be able to always see Actuals, no matter what selection of the slicer. So, they could just check Previous Month, both LA and NYC, and it would show Actuals (never goes away) and Previous Month.

The Budget Period and location are dimension tables, and the amounts are pulled from a fact table.

Slicers	Budget Period	Location
Previous Month	NYC
2024 Budget	LA
Actual

	Actual	Previous Month	2024 Budget
	NYC	LA	NYC	LA	NYC	LA
Revenue	650	450	500	300	600	400
COGS	200	150	200	175	300	250
Sales and Recruiting	50	25	50	25	75	45
G&A	20	10	30	15	15	10

[mikesdunbar]

That did it, thank you!

Could you please explain to me how that measure works from the DAX you listed out?

[DataInsights]

@mikesdunbar,

Glad to hear that worked. See comments:

Amount =
CALCULATE (
    SUM ( 'Fact Table'[Amount] ),
    // Filter 1:
    //   Step 1: Capture the selected value(s) in Budget Period slicer (disconnected table, so doesn't filter matrix).
    //   Step 2: Determine which matrix Budget Period values (dimension table joined to fact table) are in Budget Period slicer (Step 1), and return these.
    'Budget Period'[Budget Period]
        IN VALUES ( 'Budget Period Slicer'[Budget Period] )
            // OR Filter 2: This returns TRUE if Fact Table rows for Actual exist in the visual's filter context.
            || 'Budget Period'[Budget Period] = "Actual"
)

[DataInsights · Accepted solution]

@mikesdunbar,

Try this solution.

1. Create a clone of the Budget Period table and name it Budget Period Slicer (no relationships). This can be done in Power Query or a DAX calculated table. I added an Index column for sorting. Here's the model:

2. Create measure:

Amount = 
CALCULATE (
    SUM ( 'Fact Table'[Amount] ),
    'Budget Period'[Budget Period]
        IN VALUES ( 'Budget Period Slicer'[Budget Period] )
            || 'Budget Period'[Budget Period] = "Actual"
)

3. Create slicer using 'Budget Period Slicer'[Budget Period]. This is the disconnected table.

4. Configure matrix: