Forum Discussion
FotFly
Helper II
1 year agoSummarize 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 retu...
johnt75
Super User
1 year agoTry
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
Helper II
1 year agoThank you very much! I will test is out. This is a different approach that I havent thought.