Calculated Measure and Percentage is changing when used slicer and filter

[VijayAntonyM]

Team,

Good day,

I am trying to calculate the percentage value for the categories against the total value.

DAX formula is working fine but when slicer or filter values are filtered the outpwut is not chnaging and showing as it is.

I am using below DAX function 

DIVIDE(SUM(RPT_CARBON_UNPIVOT[Value]),CALCULATE(SUM(RPT_CARBON_UNPIVOT[Value]),ALL()) )

Kindly help me on this on DAX function for any kind of slicer and filter

thanks

M. Vijay Antony

[tamerj1]

Hi @VijayAntonyM 

that highly depends on the desired outcome. However, changing ALL ( ) to ALLSELECTED ( ) would most probably solve your problem 

[VahidDM · Accepted solution]

@Hi @VijayAntonyM 

The issue you're experiencing is due to the use of the ALL() function in your denominator. The ALL() function removes all filters from your data model, including those applied by slicers and filters in your report. This means the denominator remains constant, and your percentage doesn't change when you apply filters.

To make your percentage calculation responsive to slicers and filters, you should use ALLSELECTED() instead. The ALLSELECTED() function keeps the filters applied by slicers and other visuals but removes filters from the current context (like row-level filters in a table or matrix).

Here's the updated DAX formula:

Percentage =
DIVIDE(
    SUM(RPT_CARBON_UNPIVOT[Value]),
    CALCULATE(
        SUM(RPT_CARBON_UNPIVOT[Value]),
        ALLSELECTED(RPT_CARBON_UNPIVOT)
    )
)

Alternate Approach:

If you want to remove filters only from specific columns (e.g., category column) and keep others, you can use ALL() with specific columns:

Percentage =
DIVIDE(
    SUM(RPT_CARBON_UNPIVOT[Value]),
    CALCULATE(
        SUM(RPT_CARBON_UNPIVOT[Value]),
        ALL(RPT_CARBON_UNPIVOT[CategoryColumn])
    )
)

Replace CategoryColumn with the actual name of your category column.

Summary:

• Replace ALL() with ALLSELECTED() to respect slicers and filters.
• This adjustment will make your percentage values update dynamically based on any slicers or filters applied.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube