Help with measure working with slicer

[cmilligan262]

I have multiple slicers in one of my visuals. If I select a value from one slicer it filters the other slicers to only show valid options to select. 

The problem is that I have a measure that I have to calculate a percentage and to do that I have to bypass one of the slicers.

I use ALL() in the filter of a calculate expression but it by passes all one of my other slicers.

I know this is a little vauge so let me know what other information is needed to get past this.

Thank you.

[v-alq-msft · Accepted solution]

Hi, @cmilligan262 

Based on your description, you may try to use 'Allselected'. I created data as follows.

You may create two measures and compare the results to have a better understanding of them.

PercantageAllselect = 
DIVIDE(
    SUM('Table'[Score]),
    CALCULATE(
        SUM('Table'[Score]),
        ALLSELECTED('Table')
    )
)

PercentageAll = 
DIVIDE(
    SUM('Table'[Score]),
    CALCULATE(
        SUM('Table'[Score]),
        ALL('Table')
    )
)

When there is no slicer selected, the result of two measures are the same.

When you select 'c1' to filter the result, the results differ.

The denominator of 'PercentageAllselect' is calculated by (88+94), while the denominator of 'PercentageAll' is calculated by (82+91+88+69+76+94). 

ALL is a useful function that returns all the rows of a table or all the values of a column, depending on the parameter you use. What you need here is a function that does not return all of the scores, but only the ones selected in the original filter context, that is, the one of the complete pivot table. We call this kind of  computation Visual Totals, because it uses as the grand total that is visible to the user instead of the total of the complete data model. The function to use here is ALLSELECTED. 

For further information, you may refer to the following links.

https://docs.microsoft.com/en-us/dax/all-function-dax

https://docs.microsoft.com/en-us/dax/allselected-function-dax

Best Regards

Allan

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