Forum Discussion
How to exclude negative figures without impacting report results
I'm working for a FMCG currently. I have a report to track weekly sales performance. Week over week sales data is stored in this matrix by using last week stock minus this week stock. However, we have some top-up/return/new products that their last week stock is 0, after the aggregation, we got some negative figures in the matrix. The screenshot is as below:
Is there a better way to exclude these negative figures but don't impact the report? The data source doesn't have a column categorise if a product is being topped up/returned or not....
Many thanks,
Echo
Echoy51 Alternatively, you can create a measure that replaces negative values with zero:
DAX
AdjustedStockChange =
IF(
[StockChange] < 0,
0,
[StockChange]
)Hiii
Filtered_Stock_Change = IF([StockChange] < 0, BLANK(), [StockChange])If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
9 Replies
- bhanu_gautam
Super User
You can create a new calculated column that replaces negative values with zero or another value that makes sense for your analysis. Here’s an example of how to do this:
Go to the "Modeling" tab and select "New Column".
Use the following DAX formula to create the new columnAdjustedSales = IF([Sales] < 0, 0, [Sales])
If you prefer to use a measure, you can create a new measure that sums only the positive values
TotalPositiveSales = CALCULATE(SUM('SalesTable'[Sales]), 'SalesTable'[Sales] >= 0)
- Khushidesai0109
Skilled Sharer
Hiii Echoy51
You can create a calculated column
Filtered_Stock_Change =
IF('SalesData'[StockChange] < 0, BLANK(), 'SalesData'[StockChange])
ORThis measure as well
Filtered_Stock_Change =
VAR StockChange = SUM('SalesData'[StockChange])
RETURN
IF(StockChange < 0, BLANK(), StockChange)
Did I answer your question? Mark my post as a solution! And Kudos are appreciatedThis is all I have, I don't have [StockChange] column, stock change is a measurement after the aggregation and it's a dynamic metric.
Filtered_Stock_Change =
VAR StockChange = SUM('SalesData'[StockChange])
RETURN
IF(StockChange < 0, BLANK(), StockChange) this didn't work..Is any other way to achieve this?
Hi I don't have column [sales] for stock change per week. This is a measure created and is stored in the measure table.
I can't use formula Filtered_Stock_Change =
VAR StockChange = SUM('SalesData'[StockChange])
RETURN
IF(StockChange < 0, BLANK(), StockChange)and
Is there another way to solve this problem?
- bhanu_gautam
Echoy51 Alternatively, you can create a measure that replaces negative values with zero:
DAX
AdjustedStockChange =
IF(
[StockChange] < 0,
0,
[StockChange]
) - Khushidesai0109
Hiii
Filtered_Stock_Change = IF([StockChange] < 0, BLANK(), [StockChange])If this helps, I would appreciate your KUDOS!
Did I answer your question? Mark my post as a solution!
V-yubandi-msftCommunity Support
Hi Echoy51 ,
Could you confirm if bhanu_gautam & Khushidesai0109 solutions worked for you? If it did, please mark the answer as 'Accept as Solution' so others with similar queries can find it more easily. If not, please share the details.
Thank You.
- V-yubandi-msft
Hi Echoy51 ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank You.
- V-yubandi-msft
Hi Echoy51 ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.
Thank You.
