Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Kindly help me on this on DAX function for any kind of slicer and filter
thanks
M. Vijay Antony
Solved! Go to 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:
ALL()
with ALLSELECTED()
to respect slicers and filters.If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
@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:
ALL()
with ALLSELECTED()
to respect slicers and filters.If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
that highly depends on the desired outcome. However, changing ALL ( ) to ALLSELECTED ( ) would most probably solve your problem
User | Count |
---|---|
20 | |
18 | |
17 | |
11 | |
7 |
User | Count |
---|---|
28 | |
27 | |
13 | |
12 | |
12 |