Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to get SUMX Total when using iterator

Hello

 

I'm counting the number of new customers in a measure by year iterating through each Customer ID. The 1 in Number of New Customers indicates that it's a new customer. I'm using SUMX, how can I get the total?

 

Here's my code:

CALCULATE(
SUMX(
VALUES(Customer[Customer ID]),
DISTINCTCOUNT(Sales[Customer ID)
),
FILTER(
'Customer',Cust1stSalesDate >= FromSalesDate &&
Cust1stSalesDate <= ToSalesDate
)
)
 

 



  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measrue.

    Thanks.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Anonymous 

    Thank you for sharing.

    Please check the below picture and the link.

     

     

    Number of new customers total fix =
    SUMX( VALUES( data[Customer ID]), [Number of new customers])
     
    Number of new customers New version =
    VAR FromSalesDate =
    FIRSTDATE ( DimDates[Date] )
    VAR ToSalesDate =
    LASTDATE ( DimDates[Date] )
    RETURN
    COALESCE (
    SUMX (
    VALUES ( data[Customer ID] ),
    CALCULATE (
    COUNTROWS (
    FILTER (
    VALUES ( data[Customer ID] ),
    MIN ( data[Customer First Sales Date] ) >= FromSalesDate
    && MIN ( data[Customer First Sales Date] ) <= ToSalesDate
    )
    )
    )
    ),
    0
    )
     
     
     
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, Anonymous 

    If it is OK with you, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measrue.

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Jihwan_Kim 
      Thank you for replying, I have attached a sample file of what I'm trying to do. 

      Sample File 
      Please note that it has to be a measure and not a calculated column. 

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, Anonymous 

        Thank you for sharing.

        Please check the below picture and the link.

         

         

        Number of new customers total fix =
        SUMX( VALUES( data[Customer ID]), [Number of new customers])
         
        Number of new customers New version =
        VAR FromSalesDate =
        FIRSTDATE ( DimDates[Date] )
        VAR ToSalesDate =
        LASTDATE ( DimDates[Date] )
        RETURN
        COALESCE (
        SUMX (
        VALUES ( data[Customer ID] ),
        CALCULATE (
        COUNTROWS (
        FILTER (
        VALUES ( data[Customer ID] ),
        MIN ( data[Customer First Sales Date] ) >= FromSalesDate
        && MIN ( data[Customer First Sales Date] ) <= ToSalesDate
        )
        )
        )
        ),
        0
        )
         
         
         
         

        Hi, My name is Jihwan Kim.


        If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


        Linkedin: linkedin.com/in/jihwankim1975/

        Twitter: twitter.com/Jihwan_JHKIM