How to sum only the projects that end up in the red

[Gustavo_Lincoln]

Hy guys!

I was trying to create a visualization to be able to see clearly which project is paying for the new projects that can't be self sustainable yet. To do it, I have to be able to see the sum of the prejudice of all the red projects in a period of time. 

I have one big table with all the projects with the incomes and expenses in the same collum and I need to be able to sum incomes and expenses of all the projects which, in the filtered period of time, have more expenses than incomes.

I was able to only show de sum in the negative projects but i can't find a away to sum then. I believe that I would need to create a temporary table to do it but I don't know how.

Please help, I'm stuck looking for answer the whole day.

[FreemanZ · Accepted solution]

hi @Gustavo_Lincoln 

try like:

Measure =

VAR _table =

ADDCOLUMNS(

   VALUES(TableName[Project]),

   "ValueSum",

   CALCULATE(SUM(TableName[Value]))

)

RETURN

SUMX(

   FILTER(

       _table,

       [ValueSum]<0

   ),

   [ValueSum]

)

[Gustavo_Lincoln]

Thanks a lot men!

You saved me, I knew the solution had something to do with creating a table but i couldnt figure out how. I was going under a rabbit hole thinking itwould be a super complex code.

[ValtteriN]

Hi,

One way to solve this is to create a measure like this:

CALCULATE(SUM(projects[value]),ALL(projects),projects[value]<0)

This way your sum will only consider projects with negative values.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Gustavo_Lincoln]

Thank for the answer!

This wont help because my database has more than one line in each project, if i creature this measure, I will create a sum with all the negative values of all my projects and I want to create a sum of all the values of a project if the result of this is negative.

Example:

Project	Value
A	1000
A	-300
B	-500
B	200
B	-300
C	500
C	-800

The result of the measure should be the sum of projects B and C -> -900

[FreemanZ · Accepted solution]

hi @Gustavo_Lincoln 

try like:

Measure =

VAR _table =

ADDCOLUMNS(

   VALUES(TableName[Project]),

   "ValueSum",

   CALCULATE(SUM(TableName[Value]))

)

RETURN

SUMX(

   FILTER(

       _table,

       [ValueSum]<0

   ),

   [ValueSum]

)

[FreemanZ · Accepted solution]

verified like: