Forum Discussion

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. Category Item Week Site Value C1 I1 W1 S1 7 C1 I1 W1 S2 3 C1 I1 W2 S1 -...

Zubair_Muhammad

Icon for Community Champion rank

Community Champion

7 years ago

Anonymous

Try this measure

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

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
  Community Champion
  7 years ago
  Anonymous
  
  Could you share your file? Will try to help
  - Anonymous
    Not applicable
    7 years ago
    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