Forum Discussion
Table view does not show correct Measured value
- 10 months ago
Hi CornelisV
I find SUMX in your Quantity variable unnecessary since you're simply summing the quantity and not perfroming any other logic before summing. That aside, please try this:
SUMX ( -- Iterate over a table and sum the results of an expression ADDCOLUMNS ( -- Add calculated columns to an existing table SUMMARIZECOLUMNS ( Dates[Date], -- Group by Date Code[Code], -- Group by Code "@quantity", -- Create a temporary column "@quantity" CALCULATE ( SUM ( Amount[quantity] ) ) -- Sum Quantity for each Date+Code ), "@corrected", -- Add another temporary column "@corrected" SWITCH ( [Code], -- Check the value of Code 1006, 0.99, -- If Code = 1006, multiply by 0.99 1009, 0.98, -- If Code = 1009, multiply by 0.98 1025, 0.99, -- If Code = 1025, multiply by 0.99 1 -- Otherwise, multiply by 1 (no change) ) * [@quantity] -- Apply the correction factor to the quantity ), [@corrected] -- Sum the "@corrected" column across all rows )Otherwise, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.
Hi CornelisV
I find SUMX in your Quantity variable unnecessary since you're simply summing the quantity and not perfroming any other logic before summing. That aside, please try this:
SUMX (
-- Iterate over a table and sum the results of an expression
ADDCOLUMNS (
-- Add calculated columns to an existing table
SUMMARIZECOLUMNS (
Dates[Date], -- Group by Date
Code[Code], -- Group by Code
"@quantity", -- Create a temporary column "@quantity"
CALCULATE ( SUM ( Amount[quantity] ) ) -- Sum Quantity for each Date+Code
),
"@corrected", -- Add another temporary column "@corrected"
SWITCH (
[Code], -- Check the value of Code
1006, 0.99, -- If Code = 1006, multiply by 0.99
1009, 0.98, -- If Code = 1009, multiply by 0.98
1025, 0.99, -- If Code = 1025, multiply by 0.99
1 -- Otherwise, multiply by 1 (no change)
) * [@quantity] -- Apply the correction factor to the quantity
),
[@corrected] -- Sum the "@corrected" column across all rows
)
Otherwise, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.
Hi danextian ,
Thank you for your support.
The solutions works ver well.
I understand you are first creating a grouped table using the SUMMARIZECOLUMNS and then add column "@Corrected". This works if you want only create a table. Using the SUMX the DAX is transformed into Measure. Interesting learning point!
Coming back to your advice to add Excel or Pbix file, our company is not allowed to upload from cloud services, however, a by-pass using personal laptop may help. That is something to consider.
Thank you again and have a great day,
Cornelis