topic Problem with counting the quantity of customers based on their receipts in DAX Commands and Tips

Problem with counting the quantity of customers based on their receipts

Anonymous — Wed, 22 Dec 2021 08:18:26 GMT

Dears,

I encountered diffuculties in counting the quantity of customers based on their receipts. My goal is to calculate the quantity of old and new customers, who possess more than 1 receipt, two receipts and greater than 2 receipts. In addition, there is a column in my table, which divides my customers into old and new ones. Thus, I have rows with old and new ones.

I surfed the internet and unfortunately I was not able to find any solutions to handle this problem.

Therefore, the quantity of receipts was counted by this measurement:

Quantity of Receipt= DISTINCTCOUNT(Table[Receipt])

Then, I calculated the quantity of customers:

Quantity of Customers = DISTINCTCOUNT(Table[Customers])

Finally, I would like to make calculations, which show my the quantity of old and new customers which show me customers who have 1 receipt, 2 receipts and greater than 2 receipts:

Quantity of Customers with only one receipts =

VAR QuantityOfReceipts= [Quantity of Receipt]

RETURN

calculate([Quantity of Customers],filter(Table,QuantityOfReceipts= 1))

Quantity of Customers with only two receipts =

VAR QuantityOfReceipts= [Quantity of Receipt]

RETURN

calculate([Quantity of Customers],filter(Table,QuantityOfReceipts = 2))

Quantity of Customers with greater than 2=

VAR QuantityOfReceipts= [Quantity of Receipt]

RETURN

calculate([Quantity of Customers],filter(Table,QuantityOfReceipts > 2))

Unfortunately, none of these measurement works. The result of my measurements:

Would you be so kind as to share with my any ideas and DAX measurements to handle this problem?

I would appreciate it if you could assist me in dealing with it.

I am looking forward to receiving feedback from you.

Re: Problem with counting the quantity of customers based on their receipts

ValtteriN — Wed, 22 Dec 2021 09:07:04 GMT

Hi,

I think something like this should work:

Quantity of Customers with only one receipts =

VAR QuantityOfReceipts= [Quantity of Receipt]
Var QuantityBoolean = if(QuantityOfReceipts= 1,1,0)

RETURN

sumx(Table,QuantityBoolean)

I hope this helps and if it does consider accepting this as a solution!

Re: Problem with counting the quantity of customers based on their receipts

Anonymous — Wed, 22 Dec 2021 14:33:24 GMT

Hi Valtterin,

Thank you for your idea, but unfortunately it does not work. I obtained 0 results for my all clients. I would like to add that there is a table with old and new clients.

The quantitiy of these clients was measured by

Quantity of Customers = count(fSales[Client]), then I put this measurement to the table, then calculate the quantity of customers based on.

Should I use another measurements to solve this problem?

Re: Problem with counting the quantity of customers based on their receipts

PaulOlding — Wed, 22 Dec 2021 15:05:16 GMT

Hi Anonymous

The problem with your measure is it's not calculating the number of receipts for each customer.

Quantity of Customers with greater than 2= VAR QuantityOfReceipts= [Quantity of Receipt] RETURN calculate([Quantity of Customers],filter(Table,QuantityOfReceipts > 2))

The variable is calculated first and will give you the total number of receipts for all customers combined.

Here's a measure to get customers with greater than 2 receipts. Just change the filter condition in _Result to get your other measures.

Quantity of Customers with greater than 2 = VAR _RecepitsPerCustomer = ADDCOLUMNS( VALUES('Table'[Customers]), "@Receipts", [Quantity of Receipts] ) VAR _Result = COUNTROWS( FILTER(_RecepitsPerCustomer, [@Receipts] > 2) ) RETURN _Result

Re: Problem with counting the quantity of customers based on their receipts

Anonymous — Thu, 23 Dec 2021 07:37:28 GMT

Hi PaulOlding,

thank you very much for your aid and advice! It works properly!

Re: Problem with counting the quantity of customers based on their receipts

Anonymous — Thu, 23 Dec 2021 08:05:44 GMT

Hi PaulOlding, thank you for your assistence in solving my problem.

Would you be so kind as to explain me what @Receipts means in this Measurement (in addcolumns() function?

Quantity of Customers with greater than 2 = 
VAR _RecepitsPerCustomer =
ADDCOLUMNS(
    VALUES('Table'[Customers]),
    "@Receipts", [Quantity of Receipts]
)
VAR _Result = 
COUNTROWS(
    FILTER(_RecepitsPerCustomer, [@Receipts] > 2)
)
RETURN
    _Result

I would appreciate it if you could explain me in this context.

Thank you!