Power BI矩阵：使用DAX动态计算两类对应时段数值差异的方法

Got it, let’s walk through how to build this fully dynamic solution—no hardcoding required, and it’ll scale smoothly when you add months later. I’ve handled similar dynamic comparison scenarios before, so here’s the step-by-step breakdown:

Step 1: Build the Core Variance Measure #

This single DAX measure will handle all the logic: verifying exactly two categories are selected, pulling their values for the current time context (year/quarter/month), and only calculating the variance if both categories have data for that period.

Category Variance = 
VAR SelectedCats = ALLSELECTED(Table[Category])
VAR CatCount = COUNTROWS(SelectedCats)

// Only run the calculation if exactly 2 categories are picked in the slicer
IF(
    CatCount = 2,
    // Grab the two selected categories (sorted alphabetically here—adjust if needed)
    VAR Cat1 = SELECTEDVALUE(TOPN(1, SelectedCats, Table[Category], ASC))
    VAR Cat2 = SELECTEDVALUE(TOPN(1, SelectedCats, Table[Category], DESC))
    
    // Calculate each category's total for the current time period (year/qtr/month)
    VAR ValueCat1 = CALCULATE(SUM(Table[Column]), Table[Category] = Cat1)
    VAR ValueCat2 = CALCULATE(SUM(Table[Column]), Table[Category] = Cat2)
    
    // Only return the variance if both categories have non-blank data for this period
    RETURN
        IF(
            NOT(ISBLANK(ValueCat1)) && NOT(ISBLANK(ValueCat2)),
            ValueCat1 - ValueCat2, // Flip the sign here if you want Cat2 - Cat1 instead
            BLANK()
        ),
    BLANK() // Return nothing if we don't have exactly 2 categories selected
)

Step 2: Configure Your Matrix #

1. Set up your matrix with rows as Year > Qtr (when you expand to months later, just add Month to the row hierarchy—no measure changes needed).
2. Drag your original SUM(Table[Column]) measure to the Values area, and add Table[Category] to the Columns section—this will display each selected category’s value side by side.
3. Add the new Category Variance measure to the Values area. It’ll appear as an additional column showing the difference between your two selected categories.

Step 3: Hide Empty Time Periods #

To ensure only time periods with data for both categories are visible:

• Go to the Visualizations pane > Format > Visual > Row headers > Toggle "Show items with no data" to Off.
• This will automatically filter out any year/quarter/month where one or both categories have no data.

Quick Tips for Scaling to Months #

• The measure works out of the box with months—just add Table[Month] to your row hierarchy (under Qtr, or replace Qtr if you need monthly granularity). The CALCULATE function automatically respects the current month context.
• If you want to sort the selected categories by total value instead of alphabetically, adjust the TOPN logic. For example, to pick the highest-value category first: TOPN(1, SelectedCats, CALCULATE(SUM(Table[Column])), DESC).

内容的提问来源于stack exchange，提问作者user3812887