Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
VijayAntonyM
Helper I
Helper I

Calculated Measure and Percentage is changing when used slicer and filter

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()) )
 
VijayAntonyM_0-1732209775308.png

 

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

 

thanks

M. Vijay Antony

1 ACCEPTED SOLUTION
VahidDM
Super User
Super User

@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 

View solution in original post

2 REPLIES 2
VahidDM
Super User
Super User

@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 

tamerj1
Super User
Super User

Hi @VijayAntonyM 

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors