New column for total based on another table

[AliceA]

Hi all,

I think this should be obvious, but I can't figure out. Your help would be appreciated.

So, I have this table called Customers. One line per Customer.

I have a second table called Purchases. One line per Purchase; one Customer can make several purchases. Each Purchase has a 'Purchase ID' unique code.

They are linked with a 'Both' cross filtered connection using the 'Customer ID' field. 'Both', because I have to apply a filter from the 'Purchase' table and it to affect the future column.

I need to create a column in the Customer ID with the number of Purchases. A column, not a measure because I then need a second column with categories (like 'Between 1 and 10 Purchases', 'Between 10 and 20' etc.).

I can't seem to create one that is affected by the filters in the 'Purchases' table (like, say, the Purchase Date).

Can anyone help, please?

Alice

[Anonymous · Accepted solution]

Hi @AliceA ,

I tried to reproduce it.

And you can create a calculated column in Customers table to count the number of purchases.

Count = CALCULATE(COUNT(Purchases[Purchase ID]),FILTER('Purchases',[Customer ID]=EARLIER(Customers[Customer ID])))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Ashish_Mathur]

Hi,

You do not need to create a calculated column formula - a measure should suffice.  Share some data to work with and show the expected result.

[olgad]

Hi, you can create a table

SUMMARIZE(Purchases, 'Purchases'[CustomerID], "Number of Purchases", Count( 'Purchases'[CustomerID]))
Then on your customer table create a column

Column=LOOKUPVALUE('Table'[Number of Purchases], 'Table'[CustomerID],'Customer'[CustomerID])

[AliceA]

Hi Olga,

Thank you, but it doesn't look like the newly created table will be influenced by the other columns in the Purchases table... I really need this total to change based on them (such as the date).

Would you have a solution for this issue, please?

Alice

[olgad]

Apologies Alice, misread your last sentence as "you dont want the table to be affected". 

There is no such thing as a dynamic calcualted column in this tool. So let us clarify a couple of questions then,  why dont you have a separate date table? What are the fileds you want to use from purchase table to filter the Customer table? Only the date or something else?
We could create 2 measures first the count then classification based on the purchases table.

If you give a bit more detail how you intend to display it in the end and how it shall function, I can figure how to help you.