Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Grouping data & sum negatives

Hi

 

I have a table of Categoies (made up of Items), Weeks, Sites & a Value assigned to each.

 

CategoryItemWeekSiteValue
C1I1W1S17
C1I1W1S23
C1I1W2S1-15
C1I1W2S216
C1I2W1S110
C1I2W1S26
C1I2W2S1-3
C1I2W2S2-13
C2I3W1S1-5
C2I3W1S2-12
C2I3W2S111
C2I3W2S2-10
C2I4W1S1-18
C2I4W1S217
C2I4W2S1-14
C2I4W2S214

 

My dashboard can be filtered on Week & Site. What I would like to be able to is display the total negative Value, grouped at a Category level, in a Card based on other filters. For example, if I filter Week on W2:

 

CategoryItemWeekSiteValue
C1I1W2S1-15
C1I1W2S216
C1I2W2S1-3
C1I2W2S2-13
C2I3W2S111
C2I3W2S2-10
C2I4W2S1-14
C2I4W2S2

14

 

The final result I would like to see in the Card is -15 (C1 total is -15, C2 total is 1 so ignored).

 

I have got this to work in other visualisations such as Treemap & Table, by adding Category & Value and filtering with Value<0. However as far as I can see you can't filter a card by the Field it is displaying.

 

This has been driving me mental for days, so any help greatly appreciated!

Need Help

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 

     

    Try this measure

     

    Measure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( VALUES ( Table1[Category] ), CALCULATE ( SUM ( Table1[Value] ) ) < 0 )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Zubair_Muhammad  Thanks for the reply, however this only gives me the net figure at item level, ignoring the roll up to Category - unless I filter on a specific category.

       

      I'm not sure if it makes a difference, but the Category is actually a column found in another table based on a match on Item. The actual table layout is:

      Table 1 - Category, Item

      Table 2 - Item, Week, Site, Value

       

      As a default view (using the larger table in the original post), I would expect to see -17 (Sum(C2)=-17, Sum(C1)=11 so ignored) however I am actually seeing either -6 (Total NET using reply #1) or -90 (Sum of all negatives using reply #2)

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 

     

    or this one

     

    Measure 2 =
SUMX (
    FILTER (
        SUMMARIZE ( Table1, Table1[Category], "Sum", SUM ( Table1[Value] ) ),
        [Sum] < 0
    ),
    [Sum]
)