cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

Converting measure value to a negative number

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

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)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Super User IV
Super User IV

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)

---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Super User IV
Super User IV

@Marcus_E ,

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]))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Community Support
Community Support

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.

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors