Running Total Unexpect behavior

Hi Khristian ,

The issue you're describing occurs due to how filters interact with your calculations, particularly with ALL and ALLSELECTED functions in Power BI. Here's an analysis and potential fixes for your running total issue when using the "Is not" filter:

Root Cause

1. Effect of the "Is Not" Filter:

   • When you apply the "Is not" filter, it modifies the row context for the calculations, which can cause unexpected behavior in running totals. This is because the ALL function in your Running Totals measure removes some filters, but ALLSELECTED retains the context of the slicer/filter selections, including exclusions.

2. Interactions Between Measures:

   • The interaction between ALL, ALLSELECTED, and your filtering logic might be recalculating the rank or running total inconsistently for the filtered values.

Steps to Resolve

1. Update Running Total Calculation

Modify your Running Totals measure to account for filtered items explicitly. Replace ALL with a more selective filtering approach, like REMOVEFILTERS or explicitly excluding the field being filtered.

Example:

Running Totals =
VAR FilteredTable = 
    SUMMARIZE(
        FILTER(
            General,
            General[Grupo Comercial] = "mercado"
        ),
        General[Grupo Comercial]
    )
RETURN
SUMX(
    FilteredTable,
    CALCULATE(
        [Sell Out],
        REMOVEFILTERS(General[BRAND]) -- Removes brand filter but respects others
    )
)

This ensures that the measure respects filters applied to the brand, including "Is not."

2. Adjust Ranking Measure

Ensure the ranking logic excludes the filtered-out UPC values:

Ranking =
RANKX(
    FILTER(
        ALLSELECTED(General[BRAND]),
        NOT General[UPC] IN VALUES(General[UPC]) -- Exclude filtered UPCs
    ),
    [ValueToRank]
)

3. Validate the ItemsToShow Logic

In ItemsToShow, include logic to handle excluded UPCs:

ItemsToShow =
VAR TOPSELECCION = 'TOP Value'[TOP Value Value]
VAR ORDEN =
    RANKX(
        FILTER(
            ALLSELECTED(General[BRAND]),
            NOT General[UPC] IN VALUES(General[UPC]) -- Exclude filtered UPCs
        ),
        [ValueToRank]
    )
RETURN
IF(
    NOT ISBLANK([ValueToRank]),
    IF(ORDEN <= TOPSELECCION, 1, 0)
)

4. Testing and Debugging

• Add a temporary calculated column to verify which rows are included/excluded after filtering.
• Use the “Performance Analyzer” to inspect which queries are triggered and identify potential inefficiencies.

Please mark this as solution if it helps you. Appreciate Kudos.