Handling Debits and Credits in the same table

[Tob_P]

Hi,

I have a table that handles debits/credits in the same column based on a Sales Order No. I can display the SO amount (field called Amount) based on the SO No but also on a filtered level based on another field Applied To Order (which contains the Sales Order No, but will always be a negative value.

When I display the Sales Order No, it will only display the full value, not the value minus the credit. I need to figure out a way in DAX for my model display the debit value, minus the credit value, and only if there is a credit value (most of the time, there will not be). I've added a conditional column called Credit/Debit if that helps?

Anyone help me out on this or point me in the direction of the DAX function that I should be invoking so I can investigate further?

Thank you.

[Tob_P]

Thanks for replaying @n8ball - my model is not far off that, but @johnt75, your DAX suggestion seems closer to what I need and thank you too for coming back also.

Looking specifically at this part...

LOOKUPVALUE('Table'[credit value]

I tried dropping this in but used my credit/debit column in its place..

LOOKUPVALUE('NAV_Sales History MASTER'[Credit/Debit]

...but that obviously won't work, as the results in that column are text (either the word credit or debit)

Can I ask for a little more detail about what you mean about [credit value]. In my data there could be multiple different values. There isn't a credit value column as such, just negative and positive figures within the Amount column.

Hope this makes sense?

[johnt75]

The [credit value] will just be the [Amount] column, wasn't sure if it was in the same column or not.

[Tob_P]

Cheers - @johnt75 I feel like this is really close to what I need but it's coming back with an error on the measure as the 'Applied to Order' and 'Sales Order' fields are text....

Error Message:
MdxScript(Model) (86, 98) Calculation error in measure 'Key Measures'[Credit-Debit TEST]: Function 'LOOKUPVALUE' does not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

[johnt75]

I edited my original post to correctly name the Amount column and to change the [Sales Order] * -1 to string concatenation

[Tob_P]

Thanks again and I appreciate you taking the time - looks like it's not for some reason, picking up the credit...

[n8ball]

OK I see that Applies-To Order is a seperate column. Could you transform the table so that the value for the Applies-to Order SO is in the Sales Order No_ Column and then the Credit SO number is placed in a "Credit SO" column? Then the DAX I wrote before would work. 

[johnt75 · Accepted solution]

I misunderstood your original post, I thought that the sales order number for the credit included a minus. Just remove the "-" &.

[Tob_P]

That works for me! Thank you again!

[johnt75]

You could try something like

Amount inc credit = SUMX( 'Table',
'Table'[Amount] - LOOKUPVALUE('Table'[Amount], 'Table'[Applied To Order], "-" & 'Table'[Sales order])
)

[n8ball]

I would transform your table so that your debits and credits are in the same column. This makes the DAX way easier and performance is optimized. It is hard to know exactly what your data looks like but, the way I handle it in my models is to do the following:

1. My Order Value column has positive values for the Debits and negative for the Credits. Summing this column gives you the "net value"
2.  I have an order type column on my Orders fact table that indicates if the value is an Order or Return, or Credit, etc....
3. Then I have an Doc. Types dimension table that contains the Order Type descriptions and keys. This facilitates filtering
4. Then you can write measures like this to get the different flavors of your Order Value

Net Orders =
    SUM ( Orders[Order $] )

Credits =
    CALCULATE ( SUM ( Orders[Order $] ), 'Doc Types'[Order Type] = "Credit" )

Gross Orders =
    CALCULATE ( SUM ( Orders[Order $] ), 'Doc Types'[Order Type] = "Order" )

Returns =
    CALCULATE ( SUM ( Orders[Order $] ), 'Doc Types'[Order Type] = "Return" )

​