topic DISTINCTCOUNT exclude sum of negative and zero sales in DAX Commands and Tips

DISTINCTCOUNT exclude sum of negative and zero sales

Anonymous — Tue, 04 Aug 2020 01:42:47 GMT

Hello,

I am trying to calculate a number of Total /New customer.

I have a cancelled (negative) sales in my sales data.

but I would like to sum positive and negative by customer and if negative or zero I do not want to count it.

Here is my measure.

Total Customers =

CALCULATE(

DISTINCTCOUNT(SalesData[customer]),

FILTER(

SalesData,

SUM(SalesData[Sales])>0 && SalesData[customer]<>blank()

)

I would like to make my result like following.

https://drive.google.com/drive/folders/1eQKNHKmBodPbQy0v-fGVBIEDx3TM4A0n?usp=sharing

Any help would be appreciated!

Thanks,

😉

Re: DISTINCTCOUNT exclude sum of negative and zero sales

amitchandak — Tue, 04 Aug 2020 02:02:43 GMT

Anonymous , Try like

Total Customers =
countX(filter(summarize(SalesData, SalesData[customer],"_1",SUM(SalesData[Sales])+0),SUM(SalesData[Sales])>0 && not(isblank([customer]))),[customer])

Re: DISTINCTCOUNT exclude sum of negative and zero sales

Anonymous — Tue, 04 Aug 2020 02:49:29 GMT

amitchandak

Thank you for your help🙂

but did not change.

It shows same number.😅

Re: DISTINCTCOUNT exclude sum of negative and zero sales

amitchandak — Tue, 04 Aug 2020 03:10:43 GMT

Anonymous , Try like

Total Customers =
countX(filter(summarize(SalesData, SalesData[customer],"_1",SUM(SalesData[Sales])+0),[_1]>0 && not(isblank([customer]))),[customer])

Total Customers =
countX(filter(summarize(SalesData, SalesData[customer],"_1",SUM(SalesData[Sales])+0 ,"_2", calculate(SUM(SalesData[Sales])+0, allexcept(Table[Store]))),[_2]>0 && not(isblank([customer]))),[customer])

Re: DISTINCTCOUNT exclude sum of negative and zero sales

Anonymous — Tue, 04 Aug 2020 05:16:28 GMT

Hi Anonymous ,

Try this blog to understand new customer.

https://blog.enterprisedna.co/calculate-the-total-of-new-clients-youre-onboarding-every-month-advanced-dax/

Regards,

Harsh Nathani

Re: DISTINCTCOUNT exclude sum of negative and zero sales

Anonymous — Tue, 04 Aug 2020 09:27:18 GMT

First, you have to know how to calculate the measures without your condition correctly. To know this please navigate to www.sqlbi.com and look for articles on "returning customers" via the search box at the top of the page. Then, you'll need to modify the calculation a bit to factor in your condition.