Forum Discussion

CornelisV's avatar
CornelisV
Icon for Helper IV rankHelper IV
1 year 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.


There are three table, where the third table is the well-known Date table

Table Amount has the quantities of products, called ID's.

Table Code details has a list of Codes that correspondents with ID.

Table date is created with the next DAX:

 

Date =
VAR MinYear = YEAR ( MIN ( 'Amount'[Date]) )
VAR MaxYear = YEAR ( MAX ( 'Amount'[Date] ))

RETURN

ADDCOLUMNS (
 CALENDAR( MIN ( 'Amount'[date]) , TODAY() ),
 "Year", YEAR ( [Date] ),
 "Month", FORMAT ( [Date], "mmmm" )
)
 
 
I have used the next relationship:

 

Table Date is marked as datatable.

 

So far everything works, the next step is to build a DAX where the quatities are corrected:

 

Corrected Quantity =
  VAR Quantity =  SUMX(
                    'Amount',
                    'Amount'[Quantity]
                  )

 VAR Code = CALCULATE(
                   SELECTEDVALUE('Code details'[Code])
                     )
 
 VAR Correction =
           SWITCH(
            TRUE(),
            Code = 1006, 0.99,
            Code = 1009, 0.98,
            Code = 1025, 0.99,
            1
           )
             
VAR Quantity_corrected = Quantity * Correction

RETURN
    Quantity_corrected

 

 Finally the table view...

 

 

As you will see, the calculated Corrected Quantity from a measure in Table 1 and Table 2 are completely different. The corrected quantity is correct in Table 2, but not the Total amount. Table 1 has also not the cortrected Quantities. It seems that something is wrong in the DAX. Could you have a look at it what I'm doing wrong?

 

Appreciate your input,

 

Best regards,

 

Cornelis

 
Need Help
  • danextian's avatar
    danextian
    1 year 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.