Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
FotFly
Helper II
Helper II

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.

2 ACCEPTED SOLUTIONS
rajendraongole1
Super User
Super User

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

johnt75
Super User
Super User

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.

View solution in original post

5 REPLIES 5
Kedar_Pande
Super User
Super User

@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
Super User
Super User

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.

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

rajendraongole1
Super User
Super User

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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.