Summarize virtual table

[FotFly]

Hi all,

I have the following code that summarizes the existing table in my dataset and performs some calculations.

Then I want that second table to be summarized based on one column and then return the sum of a newly created column.

I think that the answer to my problem might be obvious but I cannot seem to figure it out.
Using the below expressions I get for each date the total Weighted Transactions for all dates while I would want that to be for each AsOfDate.

Here is how the dax expession looks like:

VAR Trans =

SUMMARIZE(

    Transactions,

    Transactions[EffectiveDate],

    Transactions[AsOfDate],

    Transactions[CashAmount $],

    "Weight",

        DIVIDE(

            DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate],DAY),

            DATEDIFF(EOMONTH(Transactions[AsOfDate], -3),Transactions[AsOfDate],DAY)

        ),

    "WeightedTrans",

        DIVIDE(

            DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate],DAY),

            DATEDIFF(EOMONTH(Transactions[AsOfDate], -3),Transactions[AsOfDate],DAY)

        ) * [CashAmount $]

)

VAR SumWeightTrans =

SUMMARIZE(

    Trans,

    [AsOfDate],

    "SumWeightedTrans",

    SUMX(

        Trans,

        [WeightedTrans]

    )

)
RETURN
SumWeightReturns

Thanks in advance for your help.

[rajendraongole1 · Accepted solution]

Hi @FotFly - your DAX expression to ensure the grouping within the SUMMARIZE is scoped correctly

Modified the dax as below:

VAR Trans =
ADDCOLUMNS(
Transactions,
"Weight",
DIVIDE(
DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate], DAY),
DATEDIFF(EOMONTH(Transactions[AsOfDate], -3), Transactions[AsOfDate], DAY)
),
"WeightedTrans",
DIVIDE(
DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate], DAY),
DATEDIFF(EOMONTH(Transactions[AsOfDate], -3), Transactions[AsOfDate], DAY)
) * Transactions[CashAmount $]
)

VAR SumWeightTrans =
SUMMARIZE(
Trans,
Trans[AsOfDate],
"SumWeightedTrans",
SUMX(
FILTER(Trans, Trans[AsOfDate] = EARLIER(Trans[AsOfDate])),
[WeightedTrans]
)
)

RETURN
SumWeightTrans

I hope this result in the correct sum of WeightedTrans grouped by AsOfDate.

[Kedar_Pande]

@FotFly 

Here’s the corrected DAX expression:

VAR Trans =
SUMMARIZE(
Transactions,
Transactions[EffectiveDate],
Transactions[AsOfDate],
Transactions[CashAmount $],
"Weight",
DIVIDE(
DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate], DAY),
DATEDIFF(EOMONTH(Transactions[AsOfDate], -3), Transactions[AsOfDate], DAY)
),
"WeightedTrans",
DIVIDE(
DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate], DAY),
DATEDIFF(EOMONTH(Transactions[AsOfDate], -3), Transactions[AsOfDate], DAY)
) * Transactions[CashAmount $]
)

VAR SumWeightTrans =
SUMMARIZE(
Trans,
[AsOfDate],
"SumWeightedTrans",
SUMX(
CURRENTGROUP(), -- Use CURRENTGROUP() to reference the rows within each AsOfDate group
[WeightedTrans]
)
)

RETURN
SumWeightTrans

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[johnt75 · Accepted solution]

Try

SummaryTable =
VAR Trans =
    ADDCOLUMNS (
        SUMMARIZE ( Transactions, Transactions[EffectiveDate], Transactions[AsOfDate] ),
        "@Cash Amount $", CALCULATE ( SUM ( Transactions[CashAmount $] ) ),
        "@WeightedTrans",
            DIVIDE (
                DATEDIFF ( Transactions[EffectiveDate], Transactions[AsOfDate], DAY ),
                DATEDIFF ( EOMONTH ( Transactions[AsOfDate], -3 ), Transactions[AsOfDate], DAY )
            ) * [@CashAmount $]
    )
VAR SumWeightTrans =
    GROUPBY (
        Trans,
        [AsOfDate],
        "@SumWeightedTrans", SUMX ( CURRENTGROUP (), [@WeightedTrans] )
    )
RETURN
    SumWeightReturns

The main point is to use GROUPBY rather than the second SUMMARIZE, but I've also tweaked the code a bit.

You should never use SUMMARIZE to add calculated columns, just use that for grouping and use ADDCOLUMNS to add the new columns you need.

I've also removed the Weight column as it wasn't being used, so there's no point calculating it.

Finally, I use @ in column names in temporary tables, so that they are easily distinguishable from columns or measures in the model.

[FotFly]

Thank you very much! I will test is out. This is a different approach that I havent thought.

[rajendraongole1 · Accepted solution]

Hi @FotFly - your DAX expression to ensure the grouping within the SUMMARIZE is scoped correctly

Modified the dax as below:

VAR Trans =
ADDCOLUMNS(
Transactions,
"Weight",
DIVIDE(
DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate], DAY),
DATEDIFF(EOMONTH(Transactions[AsOfDate], -3), Transactions[AsOfDate], DAY)
),
"WeightedTrans",
DIVIDE(
DATEDIFF(Transactions[EffectiveDate], Transactions[AsOfDate], DAY),
DATEDIFF(EOMONTH(Transactions[AsOfDate], -3), Transactions[AsOfDate], DAY)
) * Transactions[CashAmount $]
)

VAR SumWeightTrans =
SUMMARIZE(
Trans,
Trans[AsOfDate],
"SumWeightedTrans",
SUMX(
FILTER(Trans, Trans[AsOfDate] = EARLIER(Trans[AsOfDate])),
[WeightedTrans]
)
)

RETURN
SumWeightTrans

I hope this result in the correct sum of WeightedTrans grouped by AsOfDate.

[FotFly]

Thank you very much!

I tried that version with filter before but it was the earlier part that I was missing. I was trying to find an expression that could actually function with my virtual table column while filtering.