Forum Discussion

Leif_Eriks0n's avatar
Leif_Eriks0n
Regular Visitor
3 years ago
Solved

Trouble Linking Tables for Matrix

I am trying to link two separate tables for one matrix.

 

Table 1 has the following columns: Order Date, Time Interval (15 minutes), Venue Name, Service Type, Payment Method, Sales Amount

Table 2 has the following columns: Order Date, Time Interval (15 minutes), Venue Name, Service Type, Payment Method, Order Count

 

So, almost identical tables but one has sales amount and one has order count.  In my visual I want to show one matrix that has Venue Name for rows and Sales Amount and Order Count for values.  I also want to run a measure for average check, and I need this to be filterable by date.  My problem is, no matter how I connnect the tables (order date, venue name, etc.), I can't get both the Sales Amount and Order Count to filter at the same time.  Below is the data with no date filter:

 

Right now the tables are connected by Venue Name date slider is coming from the Table 2.  The numbers and average check calculation in the picture are correct.  However, when I move the slider to just one day (or any other range) the Order Counts change but the sales stay the same:

 

So now the transactions are correct but sales are not, and average check is thrown way off.  The same thing happens inversely if I use the order date from Table 1 in the slider - sales amount changes but the transactions do not change.

 

Does anyone know how to fix this?

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Leif_Eriks0n ,

     

    I suggest you to create dim tables like DimVenue and DimDate, then create relationships between them and Table1/Table2.

    Result in my sample is as below.

    By Default:

    In January:

    In February:

     

    Best Regards,
    Rico Zhou

     

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Leif_Eriks0n ,

     

    I suggest you to create dim tables like DimVenue and DimDate, then create relationships between them and Table1/Table2.

    Result in my sample is as below.

    By Default:

    In January:

    In February:

     

    Best Regards,
    Rico Zhou

     

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

  • Thanks, this worked.  I had to create a new/duplicate table for everything I wanted to filter on, including Payment Method, Service Type, and Time Interval.