Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
leletran
Frequent Visitor

How to handle positive and negative values in a dataset

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.

leletran_0-1685711543006.png

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.

leletran_2-1685711807287.png

 

Any insight is greatly appreciated. Thank you so much!

 

Cheers,

Lele

 

 

1 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

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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.

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

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: 

Screenshot 2023-06-05 at 9.20.05 AM.png

Absolute Value:

leletran_1-1685974739056.png

 

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

leletran_0-1685974597087.png

 

Excel:

Screenshot 2023-06-05 at 8.48.12 AM.png

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 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.