Problem with counting the quantity of customers based on their receipts

[Anonymous]

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.

L. 

[PaulOlding · Accepted solution]

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

[Anonymous]

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!

[Anonymous]

Hi PaulOlding,

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

[ValtteriN]

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!

[Anonymous]

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?

L.