Forum Discussion

Ritesh_Air's avatar
Ritesh_Air
Post Patron
6 years ago

SumX with total

Hi,

 

I am trying to find customers who bought more than $500 worth of stuff. 

 

 

SumX for More than $500 with summarize = 
SUMX (
    'CPP POS',
    CALCULATE (
        SUM ( 'CPP POS'[Sales Amount] ),
        FILTER (
            SUMMARIZE ( 'CPP POS', RETAILER[Parent Retailer AB Number] ),
            SUM ( 'CPP POS'[Sales Amount] ) > 500
        )
    )
)

 

 

It works for individual row but gives me same result of total as if $500 filter is not applied. Below:

 

 

Any help is appreciated. Thanks.

Ritesh

Need Help

6 Replies

  • FarhanAhmed's avatar
    FarhanAhmed
    Community Champion
    6 years ago

    Break your formula into two and see the results

    SumX for More than $500 with summarize = 

    CALCULATE (
        SUM ( 'CPP POS'[Sales Amount] ),
        FILTER (
            SUMMARIZE ( 'CPP POS', RETAILER[Parent Retailer AB Number] ),
            SUM ( 'CPP POS'[Sales Amount] ) > 500
        )
    )

     

    _SUMXformorethan500 = 
SUMX (
    'CPP POS',[SumX for More than $500 with summarize])

      

    • Ritesh_Air's avatar
      Ritesh_Air
      Post Patron
      6 years ago

      FarhanAhmed 

       

      Thanks. I tried that too but the problem is: if I have 2 line items for the same retailer

      which constitutes $2196 (which is sum of $1879 + 317), it filters $317 row, which I want to include.

       

      Hence I thought if I summarize it at the Retailer level then it will give me answer at that granularity.

       

      So in below example, overall total should be little higher than $749K but less than $1.04M.

       

      Thanks,

      Ritesh

       

      • FarhanAhmed's avatar
        FarhanAhmed
        Community Champion
        6 years ago

        Try Change forumla a bit 

        SumX for More than $500 with summarize = 

    CALCULATE (
       SUMX(Values( RETAILER[Parent Retailer AB Number] ),     SUM ( 'CPP POS'[Sales Amount] ) 
        )
    )


_SUM = CALCULATE( SUMX('CPP POS',[SumX for More than $500 with summarize]) , FILTER( 'CPP POS',[SumX for More than $500 with summarize]>500))
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Ritesh_Air , Try like

    sales = sumx(filter(SUMMARIZE ( RETAILER, RETAILER[Parent Retailer AB Number] ,"_1" ,SUM ( 'CPP POS'[Sales Amount] ) ),[_1]>500),[_1])


    Retailer count = countx(filter(SUMMARIZE ( RETAILER, RETAILER[Parent Retailer AB Number] ,"_1" ,SUM ( 'CPP POS'[Sales Amount] ) ),[_1]>500),[Parent Retailer AB Number])

    • Ritesh_Air's avatar
      Ritesh_Air
      Post Patron
      6 years ago

      amitchandak FarhanAhmed 

       

      unfortunately no. Both of your formulas still gives me 1.04M. Adding a new picutre to give more context. 

      The problem is: the number you are seeing on the screen : for e.g.:  $2196 could be made up of 2 transaction lines $1900 and $296,  and as shown above in one of the formulas, I do want to include everything at the grower level.

       

      I tried to change my formula in summarize to Grower instead of Retailer as well but still getting the same answer.