Running Total Unexpect behavior

[Khristian]

Hi Dudes.

I need calculate running total and filter the top 3 items for each  sub category.

Everything was running ok, but.. Sunddely the users requieres  remove a product through a filter i.e. "Is not" command into the filter pane in power bi.

Then, the total has a stranged behavior and it has taken all my week and i don´t undestand what is happend, could you help me.

i.e
The Next Table everything is ok, but, when the "itemtoshow" is applied the running total change from this:

To this

if the upc filtered is deleted every thing returns to be ok but the running total is incorrect because still contains the value of the upc that has to be filtered

My fomula for running totals is:

Ruuning Totals=

SUMX(

    SUMMARIZE(

        FILTER(General,General[Grupo Comercial]="mercado"),  General[Grupo Comercial]),

    CALCULATE(

            [Sell Out], General[Grupo Comercial]="mercado",

            ALL( General[BRAND])

        )

)

Ranking=

RANKX(

    ALLSELECTED(General[BRAND]), [ValueToRank])

ValueToRank=

SUMX(

    SUMMARIZE(

        FILTER(General,General[Grupo Comercial]<>"mercado"),  General[Grupo Comercial]),

            CALCULATE([Sell Out], General[Grupo Comercial]<>"mercado")

)

ItemsToShow=

VAR TOPSELECCION='TOP Value'[TOP Value Value]

VAR ORDEN=

RANKX(

    ALLSELECTED(General[BRAND]), [ValueToRank])

RETURN

IF( NOT ISBLANK( [ValueToRank]),

   IF(

        ORDEN<=TOPSELECCION, 1, 0)

)

Some advices, are welcome

Regards

[v-vpabbu]

Hi @Khristian,

The issue arises because of the ALL function in your Ruuning Totals measure.
ALL removes filters applied to specific columns, including the exclusion filter for UPCs, which causes the measure to include values that should be filtered out.
This behavior creates inconsistencies when filtering out specific UPCs.

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to give "Kudos"

Regards,
Vinay Pabbu

[FarhanJeelani · Accepted solution]

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.

[Khristian]

Thanks @FarhanJeelani  you ar a Titan!..

However, i made some adjusments;  but this line changed everthing

NOT General[UPC] IN VALUES(General[UPC]) 

The point, the running total was correct, but when the upc "isnot" filtered change.

I could not  create this measure because into the table (Column) Brand could not found the upc column... take a look

so, i decided add "upc" into running totals, create a new measure to calculate the total of "isnot " filtered and substract it to the running totals... and wuala!...

Running Totals New=

VAR _FilteredTable =

    SUMMARIZE(

        FILTER(

            General,

            General[Grupo Comercial] = "mercado"

        ),

        General[Grupo Comercial]

    )

VAR _Result_A=

SUMX(

    _FilteredTable,

    CALCULATE(

        [Sell Out],

        REMOVEFILTERS( General[BRAND],General[UPC])<<<== UPC Filed is added...!

    )

)

VAR _Result_B=                                                                   <<<<=== Calculate the isnot values

CALCULATE(

    CALCULATE(

        [Sell Out],

        General[Grupo Comercial]="mercado",

        NOT General[UPC] IN VALUES( General[UPC]) ),

        ALLSELECTED( General[BRAND])

)

RETURN

_Result_A-_Result_B/<<<<< Then rest the result