Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion

    Maybe:

     

    RETURN
        CALCULATE (
            SUM ( 'Dataset'[QoQ Variance] ),
           INTERSECT( VALUES( 'Dataset'[CustomerID]), topNTable
        ))

    or directly

    RETURN
        CALCULATE (
            SUM ( 'Dataset'[QoQ Variance] ),
            topNTable
        )