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.
You are calcuating corrected quantity based on code value.
For the left table, you dont have the code value and hence you get multiplying by 1. i.e., SELECTEDVALUE('Code details'[Code]) is nothing for the left table.
Define the requirements and expected output so that someone can help.
Also, paste some data of each table so that we can test. (like create sample data in EXCEL and paste here)
- CornelisV10 months agoHelper IV
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
- sevenhills10 months agoSuper User
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