Forum Discussion

charleshale's avatar
charleshale
Continued Contributor
3 years ago
Solved

Totalling selected values

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!

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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" } )
    )
)

     

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    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's avatar
      charleshale
      Continued Contributor
      3 years ago

      @jiwhan_Kim

      Perfect - you are a dax genius!  Kudos!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.