DAX Formula Problem

[CLNersesian]

Hi - 

I'm using the following DAX formula : 

Bookings Amt = CALCULATE(SUM('Sales Transaction'[Ordered Amount]), FILTER('Sales Transaction', 'Sales Transaction'[Ordered Quantity]>0))-'Sales Transaction'[Amt Invoiced]

I'm trying to only show values that are not equal to zero, or in another case not negative.

To Be Shipped Qty = CALCULATE(SUM('Sales Transaction'[Ordered Quantity]), FILTER('Sales Transaction', 'Sales Transaction'[Ordered Quantity]>=0))-CALCULATE(SUM('Sales Transaction'[Packing Slip Quantity]), FILTER('Sales Transaction', 'Sales Transaction'[Packing Slip Quantity]>=0))

I'm not sure what I'm doing wrong - so if anyone has any suggestions, please let me know. I'm not stellar with DAX!!

[v-micsh-msft]

Hi CLNersesian,

Could you please explain a bit about what the errors or issues that you are trying to resolve?

Only showing the DAX formula is not so helpful for us to understand your situations.

For the formula "Bookings Amt ", as BhaveshPatel suggested, DAX would not allow to put a column in a measure.

To have it work, either add a sum function:

Bookings Amt := CALCULATE(SUM('Sales Transaction'[Ordered Amount]), FILTER('Sales Transaction', 'Sales Transaction'[Ordered Quantity]>0))-SUM('Sales Transaction'[Amt Invoiced])

Or create a measure first:

Bookings Amt1 := CALCULATE(SUM('Sales Transaction'[Ordered Amount]), FILTER('Sales Transaction', 'Sales Transaction'[Ordered Quantity]>0))

Then another calculated column to do the minus:

Bookings Amt = [Boolings Amt1] - 'Sales Transaction'[Amt Invoiced]

For the second formula, if they are for the same products, then that is good to go, otherwise, we need to add another filter for different products.

Please reply back if you need any further assistance.

Regards

[BhaveshPatel · Accepted solution]

Hi There,

Bookings Amt = CALCULATE(SUM('Sales Transaction'[Ordered Amount]), FILTER('Sales Transaction', 'Sales Transaction'[Ordered Quantity]>0))-'Sales Transaction'[Amt Invoiced]

In the first formula, the dark black part is naked column reference and is not valid for aggregations. You should use SUM with the formula.