topic How to write a dax code that sums only greather than 0 en less than 0 in DAX Commands and Tips

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

Anonymous — Tue, 07 Apr 2020 15:43:17 GMT

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

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

Greg_Deckler — Tue, 07 Apr 2020 16:00:39 GMT

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

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

camargos88 — Tue, 07 Apr 2020 16:02:04 GMT

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

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

Anonymous — Tue, 07 Apr 2020 16:35:59 GMT

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

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

camargos88 — Tue, 07 Apr 2020 16:45:54 GMT

Anonymous ,

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

Do you have a sample data ?

Ricardo

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

Anonymous — Tue, 07 Apr 2020 17:21:37 GMT

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

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

camargos88 — Tue, 07 Apr 2020 17:34:01 GMT

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

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

Anonymous — Tue, 07 Apr 2020 17:44:09 GMT

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

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

camargos88 — Tue, 07 Apr 2020 17:49:05 GMT

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

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

Anonymous — Tue, 07 Apr 2020 17:54:24 GMT

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

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

camargos88 — Tue, 07 Apr 2020 17:54:40 GMT

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

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

Anonymous — Fri, 24 Apr 2020 14:39:38 GMT

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