Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I want to calculate the delta between Order quantity (GMEIN) and Delivered quantity (GMEIN). If it is blank, then do not perform a calculation. When I choose the Production Line in the slicer, I only want to see the difference between planned and delivered. The measure I wrote will calculate the the entire difference for the production line; I only want the difference between Order Quantiy and Delivered Quantity if there is a number in each row.
Solved! Go to Solution.
Hi Cullen,
Approach 1 - Filtering and Calculating Delta:
This approach filters out rows with zero "Delivered Qty." and calculates the difference between "Plan Order Qty." and "Delivered Qty." for the remaining rows. It's best suited for scenarios where you only want to focus on rows with partial or full deliveries.
Filter Rows Where Delivered Qty. Is Greater Than 0:
Click on the "Delivered Qty." column.
Go to the "Home" tab, then click "Number Filters" > "Greater Than."
Enter 0, then click "OK."
Calculate the Delta Between Plan Order Qty. and Delivered Qty.:
Go to the "Add Column" tab, then click "Custom Column."
In the "New Column" window:
New column name: Enter a name for the new column (e.g., "Delta")
Formula: Enter =[Plan Order Qty.] - [Delivered Qty.]
Click "OK."
Approach 2 - Conditional Calculation Without Filtering:
This approach calculates the delta between "Plan Order Qty." and "Delivered Qty." only for rows with non-zero deliveries, without removing any rows. It uses a conditional column to leave the delta blank or zero for rows with zero "Delivered Qty." This method keeps all original data and adds the calculated delta as a new column.
Add a Conditional Column to Calculate the Delta:
Go to the "Add Column" tab, then click "Conditional Column."
In the "Add Conditional Column" window:
New column name: Enter a name for the new column (e.g., "Delta")
In the "if" section:
Column: Select "Delivered Qty."
Operator: Select "is greater than"
Value: Enter 0
Output: Enter the formula =[Plan Order Qty.] - [Delivered Qty.]
In the "Else" section, you can either leave it blank or enter 0.
Click "OK."
Hi,
Please make sure you create a calculated column for this not measure.
i have placed a sample output table where i have calculated the difference.
Below is the forluma for Calculated column:
Hi Cullen,
Approach 1 - Filtering and Calculating Delta:
This approach filters out rows with zero "Delivered Qty." and calculates the difference between "Plan Order Qty." and "Delivered Qty." for the remaining rows. It's best suited for scenarios where you only want to focus on rows with partial or full deliveries.
Filter Rows Where Delivered Qty. Is Greater Than 0:
Click on the "Delivered Qty." column.
Go to the "Home" tab, then click "Number Filters" > "Greater Than."
Enter 0, then click "OK."
Calculate the Delta Between Plan Order Qty. and Delivered Qty.:
Go to the "Add Column" tab, then click "Custom Column."
In the "New Column" window:
New column name: Enter a name for the new column (e.g., "Delta")
Formula: Enter =[Plan Order Qty.] - [Delivered Qty.]
Click "OK."
Approach 2 - Conditional Calculation Without Filtering:
This approach calculates the delta between "Plan Order Qty." and "Delivered Qty." only for rows with non-zero deliveries, without removing any rows. It uses a conditional column to leave the delta blank or zero for rows with zero "Delivered Qty." This method keeps all original data and adds the calculated delta as a new column.
Add a Conditional Column to Calculate the Delta:
Go to the "Add Column" tab, then click "Conditional Column."
In the "Add Conditional Column" window:
New column name: Enter a name for the new column (e.g., "Delta")
In the "if" section:
Column: Select "Delivered Qty."
Operator: Select "is greater than"
Value: Enter 0
Output: Enter the formula =[Plan Order Qty.] - [Delivered Qty.]
In the "Else" section, you can either leave it blank or enter 0.
Click "OK."