Grouping data & sum negatives

[Anonymous]

Hi

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

Category	Item	Week	Site	Value
C1	I1	W1	S1	7
C1	I1	W1	S2	3
C1	I1	W2	S1	-15
C1	I1	W2	S2	16
C1	I2	W1	S1	10
C1	I2	W1	S2	6
C1	I2	W2	S1	-3
C1	I2	W2	S2	-13
C2	I3	W1	S1	-5
C2	I3	W1	S2	-12
C2	I3	W2	S1	11
C2	I3	W2	S2	-10
C2	I4	W1	S1	-18
C2	I4	W1	S2	17
C2	I4	W2	S1	-14
C2	I4	W2	S2	14

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:

Category	Item	Week	Site	Value
C1	I1	W2	S1	-15
C1	I1	W2	S2	16
C1	I2	W2	S1	-3
C1	I2	W2	S2	-13
C2	I3	W2	S1	11
C2	I3	W2	S2	-10
C2	I4	W2	S1	-14
C2	I4	W2	S2	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!

[Zubair_Muhammad]

@Anonymous 

or this one

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

[Zubair_Muhammad]

@Anonymous 

Try this measure

Measure =
CALCULATE (
    SUM ( Table1[Value] ),
    FILTER ( VALUES ( Table1[Category] ), CALCULATE ( SUM ( Table1[Value] ) ) < 0 )
)

[Anonymous]

@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]

@Anonymous 

Could you share your file? Will try to help

[Anonymous]

Sorry for the late reply. Here is the sort of data I am using and a mocked up dashboard

Power BI Data

Once the file is opened, the label should be displaying the sum of the boxes above. The one on the left is correct (sum of all the column), but the one on the right should sum all negative categories (-34061.3)

Thanks