How to get SUMX Total when using iterator

[Anonymous]

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 · Accepted solution]

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 · Accepted solution]

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

)

https://www.dropbox.com/s/7ha3qx430og5tme/lefli_sumx.pbix?dl=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

[Jihwan_Kim · Accepted solution]

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]

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 · Accepted solution]

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

)

https://www.dropbox.com/s/7ha3qx430og5tme/lefli_sumx.pbix?dl=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 · Accepted solution]

Thank you that worked! @Jihwan_Kim