topic Re: Running Total not working due to Filter Pane in DAX Commands and Tips

Running Total not working due to Filter Pane

beginner_dax — Thu, 23 Jan 2025 15:52:03 GMT

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.

Re: Running Total not working due to Filter Pane

Sahir_Maharaj — Thu, 23 Jan 2025 20:20:18 GMT

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])

Re: Running Total not working due to Filter Pane

beginner_dax — Thu, 23 Jan 2025 22:39:58 GMT

Hi Sahir_Maharaj

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

Re: Running Total not working due to Filter Pane

Anonymous — Fri, 24 Jan 2025 05:34:05 GMT

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

Re: Running Total not working due to Filter Pane

beginner_dax — Fri, 24 Jan 2025 13:33:47 GMT

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.