Calculation between three related tables

[nuuomkt]

Hi community, 

I have three tables, [Allusers], [Lapsedusers], and [Type]

[Allusers] has columns such as userID, contract name, start date, end date, and salespersons. 

[Lapsedusers] is extracted from [Allusers], with an extra column: lapsed date.

[Type] denotes the different type of each contract, for example, 1-year/2-year. 

[Type] relates to both [Allusers] and [Lapsedusers] by the contract name. 

Now I want to calculate the lapsed user rate of each salesperson, subgrouped by how long the user lasts(1-3months/4-6months/...) and filtered by the type of the contract on the page filter. 

However, I find out that I can't relate [Allusers] and [Lapsedusers] by userID for the ambiguity problem, so I am not able to put the numbers of lapsed users and numbers of all users in the same grid and do the calculation. 

Is there any way to build up relationships among three tables? Or should I create values to do the math?

Also, I would like to know how to apply SumX if I want to calculate the lapsed rate by month.startdate. 

Appreciate any response!

[v-frfei-msft]

Hi @nuuomkt ,

Kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

[Nathaniel_C · Accepted solution]

Hi @nuuomkt ,

There are a lot of good books on data out there. One of my favorites is either of Matt Allington's books. In them he discusses the difference between Lookup or Fact tables, and Transaction or Data Tables. The Fact tables have "facts" about an entity, such as anything having to do specifically with your user, or your salesperson, or perhaps your contract. Your data tables usually link to these fact tables and include a date. Therefore, you should have a calendar table as well. The relationship between these tables is usually from the data table (many) to the fact tables (one.) You will have one user Bill Jones ID 6 who may have many transactions as a simple example. 

Sorry this is not the easy answser that you were probably looking to find, but if you don't set up your data correctly, nothing flows correctly.  Matt does online classes as well, and no I don't get paid this, but it was super userful to spend the time!

Nathaniel

[nuuomkt]

Hi Nathaniel,

Do you mean that the real issue here is that the data is wrongly structured?  Like although I have three tables here, I should re-form them into one fact table and several data tables, and then link all the data table to the fact table?

I always just go with the original tables that I've got, and never think of this way...
Could you share some ideas on the difference between the fact table and data tables, or the requirement of each?