How to write a dax code that sums only greather than 0 en less than 0

[Anonymous]

Dear Power BI users,

I have writed a dax code/measure where I subtracted column A from B, that results in C. Now I would like to sum up only the values that are greather than zero >0 using the dax measure C. In the picture below you can see the positives and the negatives. Please help me with making a measure for this, no calculated columns please.

My measure C is: 

Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]

Measure C looks like this:      Itemnr    Price difference COGS
                                                1              20
                                                 2              10
                                                 3              0
                                                 4              -5

How can i use my current measure "Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]" to write a new measure that filters out everything above 0 that would result in 30 and the other way around (other measure) that should result in -5?

Your sincerely,
Baris

[camargos88]

Hi @Anonymous ,

You can create this measure using SUMX, for example:

X = SUMX(FILTER(TABLE; COLUMN_X > 0);  COLUMN_A - COLUMN_B)

You can check on this link: https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/dax-query-language-for-power-bi-and-power-pivot/sumx-summary-with-filter-or-additional-calculation-dax-power-pivot-power-bi/

Ricardo

[Anonymous]

Hi @camargos88 ,

Thank you for your reply, but you example is valid when there is a column_x in the table. Instead of creating a column_x i have created a measure mentioned above, this was "

Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]" = Column_x in your example. And I do have the measures Column_A and Column_B. 

Do you how to filter on this without creating an extra column_x, but using a measure.

your sincerely,

Baris

[camargos88]

@Anonymous ,

We need to test it, but I think it's possible to filter it using measures.

Do you have a sample data ?

Ricardo

[Anonymous]

Hi @camargos88 

That would be great. I have prepared the PBI file in an anonymous way. See: https://1drv.ms/u/s!ApwLzcPKGlcdhU32GNVPjFNS4f8s?e=nuAlEZ

The measure which I use are included. Please use the measure mentioned before this was "Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]". I have also added a visual on the right side. If it works correct I would like to create a measure thats sums 17288 + 12709 and another measure that sums -69584+-82624. I am aware that I can select this in the visual, but I do not want to that. Thank you in advance Ricardo!.

Yours sincerely,

Baris

[camargos88]

Hi @Anonymous ,

I didn't get your point. What do you mean by :

"If it works correct I would like to create a measure thats sums 17288 + 12709 and another measure that sums -69584+-82624. I am aware that I can select this in the visual, but I do not want to that" ??

Also, I see you are using sumx with those 2 measures, do you wanna filter the measure value > 0 ? If yes, which one ?

Ricardo

[Anonymous]

Hi @camargos88 

I would like to create a measure value >0 using the measure "

Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]". When I have created that measures >0 using "

Price difference COGS = [Old cogs x current demand]-[New cogs x current demand]" it will show only the values 17288 and 12709.

Yours sincere,

Baris

[camargos88 · Accepted solution]

@Anonymous ,

Try it:

Price difference COGS = SUMX(FILTER('Fact table'; [Old cogs x current demand]-[New cogs x current demand] > 0); [Old cogs x current demand]-[New cogs x current demand])

Ricardo

[Anonymous]

Hi,

Assume that  [Old cogs x current demand] and [New cogs x current demand] comes from different fact tables, how are we going to use the measure below to sum up everyhting above > 0. 

Price difference COGS = SUMX(FILTER('Fact table'; [Old cogs x current demand]-[New cogs x current demand] > 0); [Old cogs x current demand]-[New cogs x current demand])

Yours sincerely,

Baris

[Anonymous]

@camargos88 

I think we are almost there, because now I would like to see the result of that two itemnr toghether (thus, summed up). When we use blank() it does not show the total sum of the two items. 

Yours sincerely,

Baris

[camargos88]

@Anonymous ,

Try this one:

Price difference COGS = SUMX(FILTER('Fact table'; [Old cogs x current demand]-[New cogs x current demand] > 0); [Old cogs x current demand]-[New cogs x current demand])

Ricardo

[Greg_Deckler]

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149