Sumifs equivalent Dax not working

[Anonymous]

Hi Everyone,

I keep getting errors on my Dax and nothing seems to work so I was wondering if someone has come across something similar.

I have a table with all the orders and customer Id's and I am trying to sum the number of orders each customer has by customer ID. Some of the orders are duplicated on the table so  I am trying a distinct count. Also, there are orders that have zero value there which I need to exclude as they are exchanges

i initially tried

column = IF('custom table'[value]=>0,

DISTINCTCOUNT('custom table'[order name]),

ALLEXCEPT('custom table','custom table'[customer email]))

that didnt seem to work and I kept getting numeric error so I then created an extra column tagging all orders above with value above zero as "processed orders" and tried to readjust my syntax but that didnt seem to work either.

column = IF('custom table'[Order type]="Processed Order",

DISTINCTCOUNT('custom table'[order name]),

ALLEXCEPT('custom table','custom table'[customer email]))

Any idea on what can I do to fix this?

Thank you in advance 🙂

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

Hi,

I have attached below an excel example of my data and what they look like.

 

what i want to get frm this calculation that I am trying to do is to be able to get a bar chart showing number of customers that has 5 purchases on theirs records,6,7,10,20 etc.in order to be able to see how many purchases do returning customers tend to have.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file and for a sample sample show the expected result.

[Anonymous]

Hey Guys,

UPDATE: I managed to get it work 🙂

Its seems that due to size of the rows the calculations werent working well but I created an extra field with an if statement which would class the row (I created the extra column because there were some zero value orders that I needed to keep) and then I filter the data though Power query saved and applied,and did an extra column with the below formula,this sorted my issue.

lets see result = CALCULATE(COUNT(ORDERS_NEW[NAME]),ALLEXCEPT(ORDERS_NEW,ORDERS_NEW[EMAIL]))

Thank you everyone for your help 🙂

[Anonymous]

Hi @Ashish_Mathur 

I cant share my PBIX files as it contains confidential data,hence I attached a sample of the data on excel.

In regards ro the desired result,i have described it quite clearly I belive,but if there is something confusing could you please tell me and maybe I can try to explain it.If I could put the desired result on PBIX file I would but in that case I wouldnt need to publish my question.

[Ashish_Mathur]

What do you mean by - "number of customers that has 5 purchases on theirs records,6,7,10,20 etc.".  On a dummy dataset, show this expected result.

Furthermore, i am not going to create a dataset and also solve your question.  The least i would expect of you is to share a dummy (but representative) dataset and show the expected result on that dummy dataset.

[aj1973]

Hi @Anonymous 

Power Query won't solve the issue as it shouldn't be used to do Calculations. Your formulas( column = IF...)  are not correct. Can you share a Sample of your model? it's better and faster to help you with your issue  

[Anonymous]

Hi,

I have attached below an excel example of my data and what they look like.

 

what i want to get frm this calculation that I am trying to do is to be able to get a bar chart showing number of customers that has 5 purchases on theirs records,6,7,10,20 etc.in order to be able to see how many purchases do returning customers tend to have.

[Jos_Woolley]

How many rows' worth of data are we talking about?

Based on what you've said, it might be preferable to do this in Power Query.

Regards

[Anonymous]

hi @Jos_Woolley,

its around 2.1 million rows.

[Anonymous]

Hi @Jos_Woolley ,

The measure wouldnt work in my case as i want after that calculation i want to sum the number of users that had 10 purchases in their records,14,15,16,17 etc.

I tried the calculated column but the syntax seems to get stick working on it for more than 30 min.

[Jos_Woolley]

Hi,

Wouldn't this make more sense as a measure, instead of a calculated column?
Measure:

Unique Orders =
CALCULATE (
    DISTINCTCOUNT ( 'custom table'[order name] ),
    'custom table'[value] > 0
)

Or, calculated column:

Unique_Orders =
CALCULATE (
    DISTINCTCOUNT ( 'custom table'[order name] ),
    FILTER (
        'custom table',
        'custom table'[customer email] = EARLIER ( 'custom table'[customer email] )
            && 'custom table'[value] > 0
    )
)

Regards