Forum Discussion

CornelisV's avatar
CornelisV
Helper IV
10 months ago
Solved

Table view does not show correct Measured value

Dear all,   I have found a different table view that contains measured calculation. This is found for a long data set, but I made the data set smaller which will hopefully be more manageable. Th...
  • danextian's avatar
    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.