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 sevenhills
Thank you and I agree that sharing files is the best option. There are limitations to upload files but we are going to have a closer look how to bypass the restrictions.
danextian has proved a good solution, but I appreciate your advice.
Best regards,
Cornelis
Glad to hear the solution has been found.
PS: As an alternative to uploading files, you can enter sample data directly into Excel and then copy and paste it into this forum post.
Note: if the table size is large and you plan to use corrected column more places, please do add as a column in PQ (my preference) or in DAX.
thanks