The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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:
Solved! Go to 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.
Proud to be a 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.
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
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.
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.
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.
User | Count |
---|---|
74 | |
70 | |
39 | |
30 | |
28 |
User | Count |
---|---|
104 | |
95 | |
51 | |
48 | |
46 |