calculated column

[Cyriackpazhe]

When i write a calculated column like the above, shouldn't the same value be displayed in each row since there is no filter context and all distinct values of Sales[orderdate] will be considered the virtual table. 

[quantumudit · Accepted solution]

Hello @Cyriackpazhe 

Here is the DAX you have used to create the calculated column in the table:

Col = SUMX(
    VALUES(Sales[OrderDate]),
    Sales[SalesAmount]
)

The issue with the above DAX formula is as follows:

• VALUES(Sales[OrderDate]) returns all distinct dates from the entire Sales table in the current context (which, in a calculated column, is usually the whole table).

• SUMX iterates over that list of dates, creating a row context for each distinct date. However, because you’re using Sales[SalesAmount] without a CALCULATE or filter, DAX does not automatically filter Sales[SalesAmount] to that single date.

• Result: For each distinct date in VALUES(Sales[OrderDate]), DAX is still seeing the single row context of the calculated column. The net effect is that you often end up with a sum (SalesAmount in the current row) multiplied by the count of distinct dates (in the entire table).

If you want the sum of SalesAmount by each date within that iteration, you’d typically wrap it in CALCULATE, for example:

Col = CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        Sales,
        Sales[OrderDate] = EARLIER(Sales[OrderDate])
    )
)

You may also use VAR and RETURN instead of EARLIER() as well

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

Proud to be a Super User