Measure Changes When I add a field to my table visualization

[kjanse]

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!

[bhanu_gautam · Accepted solution]

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

[v-xingshen-msft]

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.

[divyed · Accepted solution]

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

OR

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

[bhanu_gautam · Accepted solution]

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