Table view does not show correct Measured value

[CornelisV]

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

[danextian · Accepted solution]

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.

[CornelisV]

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

[sevenhills]

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)

[CornelisV]

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

[sevenhills]

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