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 ca...
  • 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

  • Anonymous's avatar
    Anonymous
    5 years ago

    Thank you that worked! Jihwan_Kim 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Thank you that worked! Jihwan_Kim