Trying to dynamically filter numerical data that is displayed in my matrix based on row header

[Flyingpanda19]

I have an issue where I'm trying to dynamically filter the data that is displayed in my matrix. If you drill up, the filter applies to the line-level totals. If you expand, the filter is applied to the individual line levels. This works if I don't hide blanks; otherwise, it will mess up my drilled-up view of my matrix. I'm aware this is due to the PO Line Number row-level data, just not sure on how to work around that to make this work on drill-up.

Scenario:

In my test case, I have a purchase order table with Purchase Order ID's, Change Numbers, Lines of the PO, and the Change Amount.

Example data directly from the data source:

PO ID: 123

Change Order: 1

Purchase Order Line: 1

Change Amount: $5000

PO ID: 123

Change Order: 1

Purchase Order Line: 2

Change Amount: $6000

I have a matrix with PO ID, Change Order Number, PO Line, and Change Amount.

I also have a data table to filter the returned data. Ideally, if we're looking at the change order level, it returns all PO's with Change Orders >= $10k. But when looking at the individual PO Lines within the Change Order, it only returns >= $10k on the lines as well. Which will return blanks, I hide blanks, my totals are obviously off.

Change Amount Filter =
DATATABLE(
"Selection", STRING,
{
{"$10k and Above"},
{"All"}
}
)

Now, on my matrix, I have a measure called "Change Amount Filtered" and the issue comes up when I put that measure onto my matrix filter and say don't show rows where this measure has blank values. If I show blank rows, it works at all levels, but because it is showing blank at lower levels (PO Line Number), hiding those will affect my total when drilled up at the Change Order Number row header.

Filtered PO Line Change Amount =
VAR SelectedFilter = SELECTEDVALUE('Change Amount Filter'[Filter Option])
VAR Threshold = 30000
RETURN
SWITCH(
TRUE(),

// This is for viewing at the PO Line number level
ISINSCOPE('Purchase Orders'[PO Line No.]) && SelectedFilter = "$10k and Above",
VAR LineTotal =
CALCULATE(
SUM('Purchase Orders'[PO_LN_CHANGE_AMOUNT]),
VALUES('Purchase Orders'[PO Line No.])
)
RETURN
IF(LineTotal >= Threshold, [PO Line Change Amount], BLANK()),

// This is for drilling up to my PO Change Order Level
NOT ISINSCOPE('Purchase Orders'[PO Line No.]) && SelectedFilter = "$10k and Above",
VAR ChangeOrderTotal =
CALCULATE(
SUM('Purchase Orders'[PO_LN_CHANGE_AMOUNT]),
VALUES('Purchase Orders'[Change_Order_Number])
)
RETURN
IF(ChangeOrderTotal >= Threshold, [PO Line Change Amount], BLANK()),

// Default return if selecting "All"
[PO Line Change Amount]
)

Tried using Gemini/ChatGPT, but just stuck in the same loop.

[DataNinja777]

Hi @Flyingpanda19 ,

In the original approach, the measure Filtered PO Line Change Amount was used together with the “Hide blanks” setting. While this correctly removed rows with amounts below the 10k threshold, it also caused totals to disappear or become inaccurate when users drilled up to higher levels of the hierarchy. This happened because Power BI calculates subtotals only from visible rows—so when all child lines were blanked out, the parent total vanished as well.

The attached pbix file uses an Include Flag measure as a visual filter instead of blanking the value measure. The flag evaluates the threshold dynamically at each hierarchy level so that lines below the threshold are excluded, but higher-level totals are still calculated correctly. Because the value measure itself remains intact (never returning blanks), totals remain accurate even when users drill up or down.

I attach an example pbix file for your reference. 

Best regards,

[Flyingpanda19]

Your example isn't working.

In your output where you flag >= $10k, the totals are not staying the same. Look at 125, #1. Goes from 32k total to 30k total. The change order total should still show 32k when drilled up. Total for the change order is 32k that's the problem I'm facing where the total will not be correct due to hiding individual items below 10k

[Ahmed-Elfeel]

Hi @Flyingpanda19,

The issue is that hiding blank rows at the PO Line level affects the rolled up totals because the matrix filter removes those rows entirely from the calculation context.

To Solve this issue you could Use HASONEVALUE instead of ISINSCOPE with conditional formatting:

Filtered PO Line Change Amount =
VAR SelectedFilter = SELECTEDVALUE('Change Amount Filter'[Filter Option])
VAR Threshold = 10000
RETURN
IF(
    SelectedFilter = "$10k and Above",
    SWITCH(
        TRUE(),
        // PO Line Level - filter individual lines
        HASONEVALUE('Purchase Orders'[PO Line No.]),
        VAR LineTotal = [PO Line Change Amount]
        RETURN IF(LineTotal >= Threshold, LineTotal, BLANK()),
        
        // Change Order Level - filter based on change order total
        HASONEVALUE('Purchase Orders'[Change_Order_Number]),
        VAR ChangeOrderTotal = 
            CALCULATE(
                [PO Line Change Amount],
                ALLEXCEPT('Purchase Orders', 'Purchase Orders'[Change_Order_Number])
            )
        RETURN IF(ChangeOrderTotal >= Threshold, [PO Line Change Amount], BLANK()),
        
        // Default case
        [PO Line Change Amount]
    ),
    // Return all if "All" selected
    [PO Line Change Amount]
)

Then, instead of filtering the matrix, use conditional formatting to hide values:

1. Remove the matrix filter on your measure

2. Use conditional formatting → Font color → Rules

3. Format values where [Filtered PO Line Change Amount] = BLANK() with white font color

Notes:

• Don't use matrix filters for this scenario they remove entire rows from context
• Use conditional formatting to visually hide values instead of actually filtering them

• Use HASONEVALUE for more reliable scope detection

• Also format the background color to white for completely hidden appearance

• You might want to add this to handle cases where users select multiple filters:

VAR SelectedFilter = SELECTEDVALUE('Change Amount Filter'[Filter Option], "All")

• This ensures it defaults to "All" if multiple selections are made.

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Flyingpanda19]

Thanks for the reply, only downside would be a bunch of blank rows but don't think there's a way around that. 

[v-veshwara-msft]

Hi @Flyingpanda19 ,

Thanks for sharing the update. You’re correct - since matrix visuals calculate totals only from visible rows, using visual-level filters (like “Hide blanks”) will remove those rows from context, which affects totals when drilling up.

As suggested above, conditional formatting is the most practical workaround in this scenario. It visually hides the values below the threshold while keeping them in the calculation context, so the totals remain accurate. However, this will still leave blank rows in the visual, as Power BI doesn’t currently support dynamic row suppression without affecting totals.

You may consider adding an idea in the Fabric Ideas - Microsoft Fabric Community to request more flexible handling of conditional visibility in matrix visuals.

Please reach out for further assistance.

Best regards,

Vinay.