Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Combine two tables (many to many relation problem)

Hi all,

 

I would like to see how many distinct A,B,C buying customers there have been in each month.

Then i want to see how many customer meetings have been booked per A,B,C customer in each month.

 

Finally i want to calculate:

number of meetings booked / number of distinct buying customers 

per country per month and per ABC category.

 

I have 3 tables

"Meetings" = Table with records of booked meetings with customers (22.000 rows)

"Buying Customers" = Table with records of each item order transaction (1.350.000+ rows) 

"SO Profit Category" = It's a table made for sorting purposes to order A,B,C customers. 

 

A,B,C customers are in the field: Profit Category

Business Unit and Country2 is the country name. They are 100% spelled same way in both tables. 

 

But i can't join these two tables as i want. And i don't know what to do to fix it 😞 

 

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , Bring A,B,C customers from Profit Category to Customer, but that would only be one values

     

    example : correct column names

     

    Maxx(filter('Profit category', 'Profit category'[Business Unit] = customer[Business Unit] && 'Profit category'[Country2] = customer[Country]), [A,B,C customers])

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Maybe i am approaching this problem incorrectly - its not about connecting tables? 

    The customers don't necessarily have to be the same customers.

     

    Its just number of meetings held with ANY A customer

    divided by

    number of meetings budget (which is same as number of distinct A buying customers per month per country).

     

    So the specific customer don't matter only whether its an A, B or C customer and their country.