Running Total not working due to Filter Pane

[beginner_dax]

Hi everyone,

I am barely new to Power BI and I'm currently working on this Running Total calculation that is just not working for me.
The idea of it is to run sums ( %AVG ) on a 2-level matrix Order by SumAVG  and in Reverse. There is also a filter InMarkets on Filter Pane that whenever is applied it just messes with the calculation and it fixes when it's removed.

I've tried working with this idea I got from somewhere else but I can't seem to grasp the definitive solution (I couldn't find the link to show the source, I'm sorry...)

RT =

VAR Table1 =

    SELECTCOLUMNS (

        ALL ( 'FACT_TABLE'[MARKET_DSC] ),

        "PrcAVG" , CALCULATE ( [%AVG] ),

        "Sorted" , CALCULATE ( [SumAVG] )

    )

VAR Table2 =
FILTER (
            T1 , [Sorted] >= CALCULATE ( [SumAVG] )
)

RETURN

SUMX (
             Table2,
             [PrcAVG]
)

This is the measure for SumAVG

Sumavg =

 SUMX (

    SUMMARIZECOLUMNS (

        'FACT_TABLE'[MARKET_DSC],

        'FACT_TABLE'[WEEK],

        "Average", [AVG_]

        ),

        [Average]

)

This is the measure for %AVG

%AVG =

VAR TotalGs =
CALCULATE ( [SumAVG],
                     ALLSELECTED ( 'FACT_TABLE'[MARKET_DSC],
                                              'FACT_TABLE'[WEEK] )
)

RETURN

 DIVIDE ( [SumAVG] , TotalGs , 0 )


I've tried using SUMX + WINDOW , RUNNINGSUM as a Visual Calculation, but I'm not getting anywhere either

SUMX (
             WINDOW (
                                1, abs ,
                                0, rel ,
                                ORDERBY ( [SumAVG] ,DESC )
             ) ,
               [%AVG]
)


I appreciate any help I can get on this, almost going crazy with it.

[Anonymous]

Hi,

Thanks for the solution Sahir_Maharaj  offered, and i want to offer some more information for user to refer to.

hello @beginner_dax , based on your code, do you want to accumulate the  [%AVG] by the [SumAVG] soring?

Best Regards!

Yolo Zhu

[beginner_dax]

Hello @Anonymous . Indeed, I am trying to accumulate [%AVG] based on [SumAVG]. I believe the issue I'm coming across is the InMarkets in the Filter Pane, when it's applied then the calculation doesn't work.

[beginner_dax]

Hi @Sahir_Maharaj 

I tried the approach and got Resources Exceeded error. I tried narrowing the data but still getting the same result.

[Sahir_Maharaj]

Hello @beginner_dax,

Can you please try this approach:

RT = 
VAR CurrentMarket = MAX('FACT_TABLE'[MARKET_DSC])
VAR CurrentSumAVG = CALCULATE([SumAVG])

VAR FilteredTable =
    FILTER(
        ALLSELECTED('FACT_TABLE'),
        'FACT_TABLE'[MARKET_DSC] <= CurrentMarket
        && [SumAVG] >= CurrentSumAVG
    )

RETURN
SUMX(FilteredTable, [%AVG])