How to handle positive and negative values in a dataset

[leletran]

Hello all,

I'm trying to replicate a report called Material Transactions that contain both positive and negative values in some columns. For an example, if I move quantity to a new location, say 2, it will show as -2 on the report. If I add quantity, the report will reflect this as positive. Same with posted amount - could either be positive or negative. This is by design. I was wondering if there's a better way to handle this and still retain the correct total quantity and total posted amount?

I can transform both the Total Posted and Total Qty columns to Absolute Value, but the results would be incorrect. Sometimes, there's an outlier such as Jul-22.

One of my users replicated this report in Excel and well, it's easier to simply remove the minus symbol. I'd like to match his report is possible.

Any insight is greatly appreciated. Thank you so much!

Cheers,

Lele

[v-jingzhang]

Hi @leletran 

Sorry I may not understand your expected result correctly. Why do you think transforming the column to Absolute Value will make the results incorrect? Per my understanding, you want to remove the minus symbol for negative values and keep positive values as they are, don't you? If so, Absolute Value seems to be the easiest solution. 

If I understand it incorrectly, can you share some expected result based on some positive and negative values? 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[leletran · Accepted solution]

Hi @v-jingzhang,

I resolved the issue. All I had to do was do a sum of TotalPosted and TotalQty, then do a measure using ABS syntax (TotalPosted_Positive = ABS([TotalPosted]). Now my chart doesn't look like we're constantly in the negative. I was overcomplicating this... I blame Monday mornings. Many thanks for your time! Have a great one!

Cheers,

Lele

[leletran]

Hi @v-jingzhang,

Good morning and thanks so much for your response! My apology, I might not have explained well. I want to keep both positive and negative values so they can be totaled for each month. Using Absolute Value would change the Total Posted and Total Qty amount. For example, in the Excel report, let's use Feb-23 and Mar-23 as an example, the correct amount is -514 and -47 for Total Qty, respectively. The user simply add all the quantity and total posted for the month, and remove the negative symbol to make the value a "positive". (Almost all the months are negatives except for Apr-22 for Total Posted, and Jul-22 for Total Posted and Total Qty). Using Absolute Value, Feb-23 and Mar-23 is now 2,514 and 49 for Total Qty, respectively. Total Posted is also incorrect. 

Excel: 

 

Absolute Value:

I wanted to handle both negative and positive values so when I build my chart, it doesn't look like we're in constant negative! If possible, would like for both TotalQty and TotalPosted bars to be side-by-side like the original.

Current chart (no Absolute Value):

Excel:

 

I hope this was as clear as mud 😅. Please let me know if additional clarification is needed. This report can be a bit confusing. Thank you!

Cheers,

Lele