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'm struggling to resolve an issue I have with a calculated measure returning a positive value when it's calculating using two negative inputs.
We issue out materials to order numbers and I want to calculate the total value of materials issued to orders. There are two input measures that work as they should: Material Quantity and Unit Price. The final measure I'm struggling with is called Extended Price: Extended Price = [Unit Price]*[Material Quantity].
This works fine when the Unit Price or Material Quantity are positive numbers, however the problem is when either are negative (when a material item is removed or credited off an order number it will display in the transaction as a negative. This in itself is fine but I need the calculated Extended Price measure to reflect the negative result and currently when both the Unit Price and Material Quantity are negative, the Extended Price result is showing as positive.
Am I missing something here, and what can anyone suggest that I do to ensure the Extended Price returns a negative value when either or both the Unit Price or Material Quantity are negative (usually it's both negative).
Thank you.
Solved! Go to Solution.
Well, you are asking to change the rules of math. -1 * -1 = 1. Multiplying two negatives creates a positive. That's just math man.
However, if you want to defy tens of thousands of years of mathematics, I can respect that. Boo...Math!!
Extended Price =
VAR __ExtendedPrice = [Unit Price] * [Material Quantity]
RETURN
IF([Unit Price] < 0 && [Material Quantity] < 0, -1 * __ExtendedPrice,__ExtendedPrice)
Hi @Marcus_E ,
I am not clear about your requirement, if possible could you please inform me more detailed information(such as your expected output and your sample data (by OneDrive for Business))? Then I will help you more correctly.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try like
sumx(Table, Switch(true(),
Table[Unit Price]<0 && Table[Material Quantity] <0 , -1*Table[Unit Price] * Table[Material Quantity] ,
Table[Unit Price] * Table[Material Quantity]))
Well, you are asking to change the rules of math. -1 * -1 = 1. Multiplying two negatives creates a positive. That's just math man.
However, if you want to defy tens of thousands of years of mathematics, I can respect that. Boo...Math!!
Extended Price =
VAR __ExtendedPrice = [Unit Price] * [Material Quantity]
RETURN
IF([Unit Price] < 0 && [Material Quantity] < 0, -1 * __ExtendedPrice,__ExtendedPrice)
Thanks, I'm well aware of the said rules of basic mathmetics - my question was how to portray the negative value as a negative in order to report the final total accurately.
Thanks again.
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |