Totalling selected values

[charleshale]

Let's say I have a table named FRUIT that looks like this:

TYPE    QUANTITY

Bananas   2

Oranges   3

Kiwi          4

My goal is to Sum only Bananas and Oranges and have those sums show only when they are selected in a visual filter.   No problem!   All I have to do is write the following measure:

FruitSum =
VAR _Sum =
    CALCULATE ( SUM ( Fruit[Quantity] ) )
RETURN
    IF ( SELECTEDVALUE ( Fruit[Type] IN { "Bananas", "Oranges" } ), _Sum, BLANK () )

This works....but there is a catch:    I can't get the Total to show in a matrix visual (or any other way).

In other words, if I have a filter on the visual that shows only Bananas and Oranges, I get 

Bananas   2

Oranges   3

Total      

However what I would like is to show the total....

Bananas   2

Oranges   3

Total      5

Note - this outcome would be acceptable too

Bananas   2

Oranges   3
Kiwis       4 

Total      5

Thoughts on how to code?   Thank you!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

expected measure one: = 
CALCULATE (
    SUM ( Fruit[Quantity] ),
    KEEPFILTERS ( Fruit[Type] IN { "Bananas", "Oranges" } )
)

expected measure two: = 
IF (
    HASONEVALUE ( Fruit[Type] ),
    SUM ( Fruit[Quantity] ),
    CALCULATE (
        SUM ( Fruit[Quantity] ),
        KEEPFILTERS ( Fruit[Type] IN { "Bananas", "Oranges" } )
    )
)

[charleshale]

@jiwhan_Kim

Perfect - you are a dax genius!  Kudos!

[raimon]

Hi @charleshale, 

Not sure if I understood properly. Since if you are selecting values in the visual filter, that should automatically filter down the sum of values. 

However try this - 

1. Create a list that will return all values selected - 

List = CONCATENATEX(values('Fruit'[TYPE]),'Fruit'[TYPE],",")

2. Create a measure for the totals- 

Total = CALCULATE(SUM('Fruit'[QUANTITY]),FILTER('Fruit','Fruit'[TYPE] in {[List]}))

Let me know if this is what you are after.