Forum Discussion
Anonymous
4 years agoNot applicable
filtering Top N with Calculate
I have created a VAR of top N table of customers and, got one column of the desired customers,
however, when I try to calculate a sum of sales (which is called [QoQ Variance]), using an "IN" statement it won't work.
top20Customer =
VAR topNTable =
TOPN (
20,
SUMMARIZE ( 'Dataset', 'Dataset'[CustomerID] ),
CALCULATE (
SUM ( 'Dataset'[QoQ Variance] ),
DATESBETWEEN (
'Dataset'[Current Period],
DATE ( 2019, 02, 01 ),
DATE ( 2021, 01, 01 )
),
MONTH ( 'Dataset'[Current Period] ) IN { 3, 6, 9, 12 },
'Dataset'[RetentionCategory] = "Increase"
)
)
RETURN
CALCULATE (
SUM ( 'Dataset'[QoQ Variance] ),
'Dataset'[CustomerID] IN topNTable
)
Maybe:
RETURN CALCULATE ( SUM ( 'Dataset'[QoQ Variance] ), INTERSECT( VALUES( 'Dataset'[CustomerID]), topNTable ))or directly
RETURN CALCULATE ( SUM ( 'Dataset'[QoQ Variance] ), topNTable )
1 Reply
- PaulDBrown
Community Champion
Maybe:
RETURN CALCULATE ( SUM ( 'Dataset'[QoQ Variance] ), INTERSECT( VALUES( 'Dataset'[CustomerID]), topNTable ))or directly
RETURN CALCULATE ( SUM ( 'Dataset'[QoQ Variance] ), topNTable )