Show the sum of unselected values

[Anonymous]

Hey guys

How can I perform the sum of values that are not selected? For instance, in this very simple document I created a table that contains only three columns, and what I aim to do is to compute the sum of the selected values on the category slicer and show a specific number for the non-selected ones (as an example, lets say 1).

The Book Category filters the subcategory Brand 1 and 2; Pencil, Brand 3 and 4; lastly, Scissor filters brands 5 and 6. 

Now, here follows an example of what I want to get off a measure:

If I filter Pencil, then the result would be:

Subcategory       Measure

Brand 1                     1.0

Brand 2                     1.0

Brand 3                     56.60

Brand 4                     82.77

Brand 5                     1.0

Brand 6                     1.0

And the same for Scissors.

I've tried computing the result by using ISFILTERED(), however it did not work.

Measure = 
var soma = SUM('Fact'[Cost])
var selecionados = IF(ISFILTERED('Fact'[Category]), soma, 1)
return selecionados

Any thoughts on how to do that?

Thanks in advance.

[Jihwan_Kim · Accepted solution]

Hi,

One of ways to achieve this is to have disconnected slicer like below.

Please check the below picture and the attached pbix file.

Expected result measure: =
IF (
    HASONEVALUE ( Data[Subcategory] ),
    IF (
        COUNTROWS (
            FILTER ( Data, Data[Category] IN DISTINCT ( 'New Slicer'[Category] ) )
        ) >= 1,
        SUM ( Data[Measure] ),
        1
    )
)

[Anonymous]

Thank you very much, buddy! It really worked.

A while ago I've posted a topic akin to this very one, and when I tried to replicate the formula you posted above it has not worked (but indeed worked for this current topic). Do you know why?

Once again, thanks for your response.

Kudos to you!