Forum Discussion
IGNORE NEGATIVE VALUES
Hi, I have matrix visual which has negative values and these are being included in the total on the table, how can i get it to ignore the negative values, all of these below are measures
Hi Julier ,
To ensure that negative values are ignored while maintaining a correct total in the matrix visual, you need to modify your DAX formula so that only positive values contribute to the sum. Instead of simply checking if the total is greater than zero, which could cause incorrect grand totals, you should exclude negative values at the row level using SUMX. This ensures that both individual row values and the grand total are calculated correctly.
Total Ordered (KSM) = SUMX( VALUES('YourTable'[KeyColumn]), -- Replace 'YourTable' with your actual table name VAR Total = [Invoiced (KSM)] + [Unfulfilled Orders (KSM)] + [S&OE Reforecast Orders (KSM)] RETURN IF(Total > 0, Total, 0) )This approach iterates over each unique row in the specified table and sums only positive values. If a row-level total is negative, it is replaced with zero, ensuring that negative values do not contribute to the final result. Alternatively, if you are working directly with measures and want to prevent individual components from contributing negative values, you can use the MAX function to treat negative values as zero before summing.
Total Ordered (KSM) = MAX([Invoiced (KSM)], 0) + MAX([Unfulfilled Orders (KSM)], 0) + MAX([S&OE Reforecast Orders (KSM)], 0)This method ensures that none of the individual measures contribute negative amounts. However, it does not filter them out at the row level, making the SUMX approach a more robust solution for accurate grand totals. Let me know if you need any refinements based on your specific dataset.
Best regards,
4 Replies
Hi Julier ,
To ensure that negative values are ignored while maintaining a correct total in the matrix visual, you need to modify your DAX formula so that only positive values contribute to the sum. Instead of simply checking if the total is greater than zero, which could cause incorrect grand totals, you should exclude negative values at the row level using SUMX. This ensures that both individual row values and the grand total are calculated correctly.
Total Ordered (KSM) = SUMX( VALUES('YourTable'[KeyColumn]), -- Replace 'YourTable' with your actual table name VAR Total = [Invoiced (KSM)] + [Unfulfilled Orders (KSM)] + [S&OE Reforecast Orders (KSM)] RETURN IF(Total > 0, Total, 0) )This approach iterates over each unique row in the specified table and sums only positive values. If a row-level total is negative, it is replaced with zero, ensuring that negative values do not contribute to the final result. Alternatively, if you are working directly with measures and want to prevent individual components from contributing negative values, you can use the MAX function to treat negative values as zero before summing.
Total Ordered (KSM) = MAX([Invoiced (KSM)], 0) + MAX([Unfulfilled Orders (KSM)], 0) + MAX([S&OE Reforecast Orders (KSM)], 0)This method ensures that none of the individual measures contribute negative amounts. However, it does not filter them out at the row level, making the SUMX approach a more robust solution for accurate grand totals. Let me know if you need any refinements based on your specific dataset.
Best regards,
Hi Julier
Try this:
SUMX ( // Iterates over a filtered summary table and sums the values of [@value] FILTER ( // Filters the summarized table to include only rows where [@value] > 0 SUMMARIZECOLUMNS ( 'Table'[Column1], // Groups data by 'Column1' 'Table'[Column2], // Groups data by 'Column2' "@value", [Total Ordered (KSM)] // Creates a new column "@value" with [Total Ordered (KSM)] ), [@value] > 0 // Keeps only rows where "@value" is greater than 0 ), [@value] // Sums the "@value" column from the filtered table )The above measures creates a virtual summary table of [Total Ordered (KSM)] based on the Column1 and Column2 values (replace with actual columns). The virtual table is then filtered to keep only those with values greater than 0 and then finally summed up using SUMX.
Hi Julier ,
To exclude negative values from the total in your matrix visual, you need to modify your measure so that it only includes positive values. You can use theIForFILTERfunction in DAX to ensure that negative values are ignored. Use this,Total Ordered (KSM) = VAR InvoicedVal = IF([Invoiced (KSM)] > 0, [Invoiced (KSM)], 0) VAR UnfulfilledVal = IF([Unfulfilled Orders (KSM)] > 0, [Unfulfilled Orders (KSM)], 0) VAR ReforecastVal = IF([S&OE Reforecast Orders (KSM)] > 0, [S&OE Reforecast Orders (KSM)], 0) RETURN InvoicedVal + UnfulfilledVal + ReforecastValThis formula ensures that only positive values are included in the total calculation, and any negative values are treated as zero.
Hello Julier ,
You can use below dax to ignore negative values :
Total Ordered (KSM) =
IF([Invoiced (KSM)] > 0, [Invoiced (KSM)], 0) +
IF([Unfulfilled Orders (KSM)] > 0, [Unfulfilled Orders (KSM)], 0) +
IF([S&OE Reforecast Orders (KSM)] > 0, [S&OE Reforecast Orders (KSM)], 0)OR
Total Ordered (KSM) =
MAX([Invoiced (KSM)], 0) +
MAX([Unfulfilled Orders (KSM)], 0) +
MAX([S&OE Reforecast Orders (KSM)], 0)I hope this helps.
Did I answer your query ? Mark this as solution if this helps, Kudos are appreciated.
Warm Regards,
Neeraj
