Distinct Count based on the sum of a metric

jgb231 — Wed, 24 Mar 2021 19:45:46 GMT

Hi,

I have a current requirement to calculate the distinct number of customers who are buying producting within a given time period. This time period could be 1 day, 10 days, monthly, quarterly, etc. The table is at the invoice line level. The requirement is that within the given time period selected, to be counted, the customer had to have done business in which the sum of the sales amount is not zero.

The current calculation is as follows:

CustomerCount:=CALCULATE(DISTINCTCOUNT(Transactions[CustomerCount]),
FILTER(Transactions,[NetSales]<>0 )
)

The filter measure is:

NetSales:=SUMX(filter(Transactions,Transactions[system_currency_adj_3rd_party_line_total_amt]<>0&&Transactions[is_revenue_ind]=1&&Transactions[Transaction_Type_Aggregate]="Net Sales"),Transactions[system_currency_adj_3rd_party_line_total_amt])

I still end up with a handful of customers that are counted because their line items are both positive (invoice) and negative (credit) but should not because their total NetSales for the given period is 0.

I tried the following:

CustomerCounttest:=
CALCULATE(
DISTINCTCOUNT( Transactions[ParentPartnerID]),
FILTER (
ALLEXCEPT(Transactions,
CALCULATE(sum(Transactions[NetInvoiceAmt]))<>0
)
)

but I end up with a memory exceeds the visualization error

This cannot be that hard of a scenario to solve for. Please note this is currently deployed in Azure Analysis Services.

Any help is appreciated!

Joe

Re: Distinct Count based on the sum of a metric

amitchandak — Thu, 25 Mar 2021 07:36:24 GMT

jgb231 , Not very clear, do you want to count customer net sales <> 0 where net sales is a measure. Ideally if there no negative amount, you are getting that only.

Otherwise you need to have a measure like

Example measure

countx(filter(Values(customer[Customer id]),[Net sales]<>0 ), [Customer id])

Try a measure like above

if not

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Distinct Count based on the sum of a metric

jgb231 — Tue, 30 Mar 2021 00:54:21 GMT

The countx did it! Thank you so much!

topic Re: Distinct Count based on the sum of a metric in DAX Commands and Tips

Distinct Count based on the sum of a metric

Re: Distinct Count based on the sum of a metric

Re: Distinct Count based on the sum of a metric