topic Re: Measure Changes When I add a field to my table visualization in DAX Commands and Tips

Measure Changes When I add a field to my table visualization

kjanse — Fri, 14 Feb 2025 02:11:19 GMT

When I add certain fields to add additional details to a visual, my measure that is a cumulative sum of balances based on a fiscal year selection changes the sum and totals. Here is the formula that I'm currently using:

CALCULATE(

SUM([CUMULATIVE_BALANCE]),

FILTER(

ALL('RER_EXP'[FY]),

'RER_EXP'[FY] <= MAX('RER_EXP'[FY])

)
)

Thank you for you help!

Re: Measure Changes When I add a field to my table visualization

bhanu_gautam — Fri, 14 Feb 2025 03:48:30 GMT

kjanse When you add additional fields to your table visualization, it changes the context of the calculation, which can affect the results of your cumulative sum measure.

To ensure that your cumulative sum measure calculates correctly regardless of the additional fields, you can modify your DAX formula to explicitly define the context for the fiscal year

DAX
CALCULATE(
SUM([CUMULATIVE_BALANCE]),
FILTER(
ALLSELECTED('RER_EXP'[FY]),
'RER_EXP'[FY] <= MAX('RER_EXP'[FY])
)
)

Re: Measure Changes When I add a field to my table visualization

divyed — Fri, 14 Feb 2025 07:02:48 GMT

Hello kjanse ,

The issue you're describing typically occurs when the context of the visual changes due to adding more fields, especially if those fields interact with your measure. You can modify your dax as below to solve this issue :

Cumulative Balance Remove Filters =
CALCULATE(
SUM([CUMULATIVE_BALANCE]),
REMOVEFILTERS('RER_EXP'[FY]),
'RER_EXP'[FY] <= MAX('RER_EXP'[FY])
)

CALCULATE(
SUM([CUMULATIVE_BALANCE]),
FILTER(
ALLSELECTED('RER_EXP'[FY]),
'RER_EXP'[FY] <= MAX('RER_EXP'[FY])
)
)

Did I answer your query ? Mark this as solution if this has solved your issue, Kudos are appreciated.

Warm Regards,

Neeraj

Re: Measure Changes When I add a field to my table visualization

Anonymous — Mon, 17 Feb 2025 02:52:06 GMT

Hi All,
Firstly divyed and bhanu_gautam thank you for yours solutions！
And kjanse ,Based on your question, I think you have to visualize the object is composed of data from two tables, then in this case, you use the ALL function, but you only exclude the impact of the filter of the column FY, at this time if you add other columns into the case, it will affect your cumulative total, you can use ALL directly on this table to ensure that your context will not be affected, if you still need to use the If you still need to use the FY column, then the suggestions given by the two SUs are great and effective, and can solve your needs perfectly, I hope this explanation helps you!

DAX = CALCULATE ( SUM ( [CUMULATIVE_BALANCE] ), FILTER ( ALL ( 'RER_EXP' ), 'RER_EXP'[FY] <= MAX ( 'RER_EXP'[FY] ) ) )

MEASURE = CALCULATE ( SUM ( [CUMULATIVE_BALANCE] ), ALLEXCEPT ( RER_EXP, 'RER_EXP'[FY] ), 'RER_EXP'[FY] <= MAX ( 'RER_EXP'[FY] ) )

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.