Forum Discussion
Table Relationship
Hi
I have 2 tables, Table A comes from database, and Table B comes from Google Analytics. Table A has the total number of registration Country, Device and Source wise, and Table B has total number of users Country, Device and Source wise. I have a line graph that shows the last 7 days rolling avearge registration rate, formula for which is "total number of registraton" (From Table A) divide with the "total number of users" (From Table B).
I want to add 3 more metrics to my dashboard
1. Registration Source wise
2. Registration Country wise
3. Registration Device wise.
I want the formula to work in such a way that it divides the total number of particular source e:g google with total number of registrations done through google and so on. and this should apply to Device and source as well.
What is the best way to do this. What all relationship should be created to get the end result..
faisayed , You need to create common country, source, use, and date table, and then you can create rolling measure using the date table
Rolling 7 = CALCULATE(Sum(Table1[Reg Count]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
Rolling 7 user= CALCULATE(Sum(Table2[User Count]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
refer
2 Replies
faisayed , You need to create common country, source, use, and date table, and then you can create rolling measure using the date table
Rolling 7 = CALCULATE(Sum(Table1[Reg Count]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
Rolling 7 user= CALCULATE(Sum(Table2[User Count]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
refer
Thank You amitchandak for quick response and excellent workaround for my problem.
I got the outcome i was looking for.👍
