Forum Discussion

AliceA's avatar
AliceA
Regular Visitor
3 years ago
Solved

New column for total based on another table

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.           

5 Replies

  • olgad's avatar
    olgad
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    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's avatar
      AliceA
      Regular Visitor
      3 years ago

      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's avatar
        olgad
        Icon for Resident Rockstar rankResident Rockstar
        3 years ago

        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. 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    3 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.