Forum Discussion

phelms's avatar
phelms
Frequent Visitor
8 years ago

Show development in hitrate per day - multiple tables

Hi,

 

I am trying to show the hitrate per day in a bar chart, filtered by date.

 

The hitrate is a measure, combining data from two tables: Sales and Calls. Both tables contain multiple posts on the same date and by the same user. The measure is: Salgsforsøg% = COUNT(Sale_reg[LID1]) / sum(TLF_application_user[callsanswered])

 

I created a third table, creating a relationship between the user id from both tables to a unique user id in the third table (many to one - both ways)

 

I try to create a fourth table to sync the dates from the two tables, using: TimeDim = CALENDAR(FIRSTDATE(TLF_application_user[date]);LASTDATE(TLF_application_user[date])).

 

I cannot create a relationship between the Sales and Calls table to the TimeDim table, since this would introduce ambiguity between the two filter tables.

 

If I leave out the filter, it shows the total count of sales, divided by the filtered amount of calls answered.

 

How do I work around this problem?

2 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    phelms,

     

    Could you please elaborate your data structure? If possible provide us some sample data and screenshots, so that we can make further analysis. You can also upload a pbix file to Onedrive or any web storage and share the link. Do mask sensitive data before uploading.

     

    Regards,

    Charlie Liao

    • phelms's avatar
      phelms
      Frequent Visitor
      8 years ago

      I try to connect these two tables by date and user. The connection is many-to-many, but the tables are in different sizes (you can receive a call without making a sale). Each line in the table "Sale_reg" represent a sale.

       

      I also imported a table, containing the unique user ID's to link the tables, and I created a TimeDim table, using this formula: TimeDim = CALENDAR(FIRSTDATE(TLF_application_user[date]);LASTDATE(TLF_application_user[date])).

       

      In the relations, I know the current setup creates an ambiguity, but I can not wrap my head around how to make the connection?

      Table overviewRelation overview