Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
I misunderstood your original post, I thought that the sales order number for the credit included a minus. Just remove the "-" &.
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..
The [credit value] will just be the [Amount] column, wasn't sure if it was in the same column or not.
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.
I edited my original post to correctly name the Amount column and to change the [Sales Order] * -1 to string concatenation
Thanks again and I appreciate you taking the time - looks like it's not for some reason, picking up the credit...
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.
I misunderstood your original post, I thought that the sales order number for the credit included a minus. Just remove the "-" &.
That works for me! Thank you again!
You could try something like
Amount inc credit = SUMX( 'Table',
'Table'[Amount] - LOOKUPVALUE('Table'[Amount], 'Table'[Applied To Order], "-" & 'Table'[Sales order])
)
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:
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" )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
76 | |
50 | |
46 | |
16 | |
12 |