Calculate sum of negative amounts generated by measures

[Anonymous]

Hi all, 

Can you please help me to calculate sum of negative amounts only? 

Given three tables.

Table 1 is used to bill a customer and has Project, Item, Product type and Amount columns.

Table 2 is actual costs and has Project, Product type and Amount Colums

Table 3 has data with Project and Supervisor name

All three tables have relationship by Project 

Now having those relationships I created a visual table that has 5 columns: 1. Supervisor, 2. Project,

3. "Project type amount calculated using data from Table 1",

4. "Project type amount calculated using data from Table 2" ,

5. Difference between column 3 & 4

Now I need to create another visual table that will show Supervisor name and sum of negative amounts only from column 5 in the visual described above.

I've used this formula 

Fruit negative = SUMX(SOR, IF([Diff fruit]<0, [Diff fruit],BLANK())) but the results it the later table are incorrect. 

The correct result should be

Alex Wright -$65

Glenn Butler -$2

Peter Smith -$97

I have sample bi file that i can share with you, if you'd like to have a look at it. 

Thank you very much everyone in advance!

[Anonymous]

Hi @tamerj1 ,

Thank you very much, it worked perfectly! Would you recommend any articles or courses to understand how exactly those functions work? Have a great day 🙂

[tamerj1]

Yes sure. Start with "The definitive guide to DAX"

[tamerj1 · Accepted solution]

Hi @Anonymous 
I assume Supervisor and Project in the visual are both from table3. If so you may try

Fruit negative =
SUMX (
    SUMMARIZE ( 'Table 3', 'Table 3'[Supervisor], 'Table 3'[Project] ),
    CALCULATE ( IF ( [Diff fruit] < 0, [Diff fruit], BLANK () ) )
)