Forum Discussion
No CACULATE challenge
- 3 years ago
Hi AlexisOlson & Greg_Deckler
This one uses neither CALCULATE nor CALCULATETABLE and performs faster than the original oneSales Amount (Delivered) 2 = VAR SelectedSales = ALLSELECTED ( Sales ) VAR SummarySales = SUMMARIZE ( SelectedSales, Sales[DeliveredDateKey], "@SalesAnount", SUM ( Sales[SalesAmount] ) ) RETURN SUMX ( VALUES ( 'Calendar'[DateKey] ), VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] = 'Calendar'[DateKey] ) RETURN SUMX ( FilteredSales, [@SalesAnount] ) ) - 3 years ago
AlexisOlson Good challenge! I am a Pro CALCULATE but recommend users not to stick with a single side.
Here are my solutions:Solution 1 using INNERJOIN, performs in 40ms without any filter
Delivered Amount AS = VAR Dates = SELECTCOLUMNS ( VALUES ( 'Calendar'[DateKey] ), "DeliveredDateKey", 'Calendar'[DateKey] & "" ) VAR SalesByDelivery = SELECTCOLUMNS ( SUMMARIZE ( ALLSELECTED ( Sales ), Sales[DeliveredDateKey], "Sum", SUM ( Sales[SalesAmount] ) ), "DeliveredDateKey", [DeliveredDateKey] & "", "Sales", [Sum] ) VAR Result = SUMX ( NATURALINNERJOIN ( Dates, SalesByDelivery ), [Sales] ) RETURN ResultSolution 2 using CONTAINSROW or the IN performs in 23 ms
Delivered Amount AS 2 = VAR SalesByDeliveryDate = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "TotalSales", SUM ( Sales[SalesAmount] ) ) VAR CurrentYearRows = FILTER ( SalesByDeliveryDate, CONTAINSROW ( VALUES ( 'Calendar'[DateKey] ), Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( CurrentYearRows, [TotalSales] ) RETURN ResultCumulative Total - I added a YearMonth column in the Dates table, since we are not showing date level I decided to reduce granularity, takes about 50ms.
CT DeliveredAmount AS = VAR SalesByDelivery = SUMMARIZE ( ALL ( Sales ), Sales[DeliveredDateKey], "@TotalSales", SUM ( Sales[SalesAmount] ), "@YearMonth", YEAR ( Sales[DeliveredDateKey] ) * 100 + MONTH ( Sales[DeliveredDateKey] ) ) VAR Result = SUMX ( FILTER ( SalesByDelivery, [@YearMonth] <= MAX ( 'Calendar'[YearMonth] ) ), [@TotalSales] ) RETURN Resulttamerj1 I tried and your code and it returns incorrect subtotals.
AlexisOlson Good challenge! I am a Pro CALCULATE but recommend users not to stick with a single side.
Here are my solutions:
Solution 1 using INNERJOIN, performs in 40ms without any filter
Delivered Amount AS =
VAR Dates =
SELECTCOLUMNS (
VALUES ( 'Calendar'[DateKey] ),
"DeliveredDateKey", 'Calendar'[DateKey] & ""
)
VAR SalesByDelivery =
SELECTCOLUMNS (
SUMMARIZE (
ALLSELECTED ( Sales ),
Sales[DeliveredDateKey],
"Sum", SUM ( Sales[SalesAmount] )
),
"DeliveredDateKey", [DeliveredDateKey] & "",
"Sales", [Sum]
)
VAR Result =
SUMX (
NATURALINNERJOIN ( Dates, SalesByDelivery ),
[Sales]
)
RETURN
Result
Solution 2 using CONTAINSROW or the IN performs in 23 ms
Delivered Amount AS 2 =
VAR SalesByDeliveryDate =
SUMMARIZE (
ALL ( Sales ),
Sales[DeliveredDateKey],
"TotalSales", SUM ( Sales[SalesAmount] )
)
VAR CurrentYearRows =
FILTER (
SalesByDeliveryDate,
CONTAINSROW (
VALUES ( 'Calendar'[DateKey] ),
Sales[DeliveredDateKey]
)
)
VAR Result =
SUMX ( CurrentYearRows, [TotalSales] )
RETURN
Result
Cumulative Total - I added a YearMonth column in the Dates table, since we are not showing date level I decided to reduce granularity, takes about 50ms.
CT DeliveredAmount AS =
VAR SalesByDelivery =
SUMMARIZE (
ALL ( Sales ),
Sales[DeliveredDateKey],
"@TotalSales", SUM ( Sales[SalesAmount] ),
"@YearMonth", YEAR ( Sales[DeliveredDateKey] ) * 100 + MONTH ( Sales[DeliveredDateKey] )
)
VAR Result =
SUMX (
FILTER (
SalesByDelivery,
[@YearMonth] <= MAX ( 'Calendar'[YearMonth] )
),
[@TotalSales]
)
RETURN
Result
tamerj1 I tried and your code and it returns incorrect subtotals.
AntrikshSharma
Yes you are right I've over looked that but this is an easy fix. The outer SUMX should have been a MAXX
Cumulative Sales Amount (Delivered) 2 =
VAR SelectedSales = ALLSELECTED ( Sales )
VAR SummarySales =
SUMMARIZE (
SelectedSales,
Sales[DeliveredDateKey],
"@SalesAnount", SUM ( Sales[SalesAmount] )
)
VAR SummaryDates =
SUMMARIZE (
'Calendar',
'Calendar'[Year],
'Calendar'[MonthName],
"@MaxDate", MAX ( 'Calendar'[DateKey] )
)
RETURN
MAXX (
SummaryDates,
VAR FilteredSales = FILTER ( SummarySales, [DeliveredDateKey] <= [@MaxDate] )
RETURN
SUMX ( FilteredSales, [@SalesAnount] )
)- Greg_Deckler3 years ago
Community Champion
tamerj1 Slick, way better than my revision in my video I made last night.
- tamerj13 years ago
Community Champion
Wow!
Thank you for mentioning me in your video ☺️
My name is Tamer Juma by the way and we are connected in LinkedIn actually you replied to my comments there couple of times.
Note: I fixed the wrong total by simply replacing the outer SUMX with MAXX- Greg_Deckler3 years ago
Community Champion
tamerj1 Yep, I saw that after the video went up. I posted the PBIX out on MicrosoftHatesGreg on github with the revised measure that corrects the subtotals. Absolutely, of course I'm going to mention you! You're one of the stars of the show! And nice to meet you Tamer Juma! Sorry, never put the two together before or my memory is failing me! But, it did let me work in the SwizzleStick420 joke!