topic Calculated Measure and Percentage is changing when used slicer and filter in DAX Commands and Tips

Calculated Measure and Percentage is changing when used slicer and filter

VijayAntonyM — Thu, 21 Nov 2024 17:24:10 GMT

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

Re: Calculated Measure and Percentage is changing when used slicer and filter

tamerj1 — Thu, 21 Nov 2024 18:01:37 GMT

Hi VijayAntonyM

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

Re: Calculated Measure and Percentage is changing when used slicer and filter

VahidDM — Thu, 21 Nov 2024 20:23:11 GMT

@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