DAX Formula calculate + Filter error

[Petersx]

Good Morning,

i have a problem with a formula in DAX.
I would work with a measure to sum only the negative values.

My last idea:
Test = CALCULATE(SUM(AswKpf[Restmenge]);FILTER(AswKpf[Restmenge];AswKpf[Restmenge]) > 0))

Can anybody help me?
I find only examples with date in the internet....

best regards,
Peter

 

[Sinon]

If you want to sum up all the negative values in column Restmenge from the table AswKpf, try using CALCULATE's own filters:
=CALCULATE(SUM(AswKpf[Restmenge]),AswKpf[Restmenge]<0)
If you prefer positive values instead, change the sign:
=CALCULATE(SUM(AswKpf[Restmenge]),AswKpf[Restmenge]>0)

 

[Petersx]

If you want to sum up all the negative values in column Restmenge from the table AswKpf, try using CALCULATE's own filters:
=CALCULATE(SUM(AswKpf[Restmenge]),AswKpf[Restmenge]<0)
If you prefer positive values instead, change the sign:
=CALCULATE(SUM(AswKpf[Restmenge]),AswKpf[Restmenge]>0)

thanks sinon, thats perfect.
But now, my next problem:

turnover = CALCULATE(SUM([Measure*]);[Measure]<0)

i get a error: formula can not work with true/false-expression


*Measure = Formula to calculate Value * pieces

 

[Sinon]

So you created another calculated field called "Measure"? How exactly is this calculated? Maybe a sample of your data and created measures would help. You can share those by uploading it to a cloud (like dropbox) and sharing the link.

 

[Petersx]

So you created another calculated field called "Measure"? How exactly is this calculated? Maybe a sample of your data and created measures would help. You can share those by uploading it to a cloud (like dropbox) and sharing the link.

Hello Sinon,
okey i create a example. My problem is measure "produce 2".
i can not link to a measure in this formula.


dropbox:
https://www.dropbox.com/sh/rldinmm53iv8j1g/AAC9kAID4SVCUfITnsadQ1RJa?dl=0

Mappe1.xlsx is the database

 

[Petersx]

So you created another calculated field called "Measure"? How exactly is this calculated? Maybe a sample of your data and created measures would help. You can share those by uploading it to a cloud (like dropbox) and sharing the link.

Measure is calculated with sum and min
Measure = MIN(turnover[ storage]) - SUM(turnover[orders])

 

[scottsen]

turnover = CALCULATE(SUM([Measure*]);[Measure]<0)

The 2nd param to calculate must be a "simple expression" or a table. You can't use a measure there without wrapping it in a FILTER().

turnover = CALCULATE(SUM([Measure*]); FILTER(MyTable, [Measure]<0))

 

[Petersx]

The 2nd param to calculate must be a "simple expression" or a table. You can't use a measure there without wrapping it in a FILTER().

turnover = CALCULATE(SUM([Measure*]); FILTER(MyTable, [Measure]<0))

okey thank you.

Do you have a other idea for my problem? I can not use a table or "simple expression".
Maybe this example helps:

Product | order | Warehouse
A..................40.................50
B..................50.................60
A..................50.................50
A..................10.................50
-------------------------------------------------
First Step:

Product | orders | to produce
A..................100................50
B..................50..................-10

to produce: min(warehouse) - sum(product)
--------------------------------------------------
Second Step (my problem)
summerize only negative values
Goal:
reserve on stock: -10

 

[scottsen]

Sorry, having a hard time understanding your problem.

I can see you have (40+50+10) orders for Product A, WareHouse 50.
And you have (50) orders for Product B, Warehouse 60.

I have no idea where the -10 came from.

 

[Petersx]

Sorry, having a hard time understanding your problem.

I can see you have (40+50+10) orders for Product A, WareHouse 50.
And you have (50) orders for Product B, Warehouse 60.

I have no idea where the -10 came from.

this -10 comes from:

Product B = 50 Orders and a storage with 60 pieces.
so: 50 - 60 = -10

now i would summerize only negative values
hopefully it helps