Forum Discussion

FotFly's avatar
FotFly
Helper II
1 year ago
Solved

Summarize virtual table

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.

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

     

  • johnt75's avatar
    johnt75
    1 year ago

    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.

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      FotFly
      Helper II
      1 year ago

      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.

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
      FotFly
      Helper II
      1 year ago

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

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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