Setting up and filtering by KPI Targets across multiple tables

[Anonymous]

Hi powerbiers, 

I have a need to show one target line for KPIs from a sales activity table that have multiple metrics but of which it is likely only one will be selected at anytime.  

I can't work out how to manage the relationship and allow for dynamic filtering in a chart. 

I have an activity, date and users table. The users table currently has the monthly target for each metric but isn't associated to any date. 

Heads spinning with this one.

[v-ljerr-msft]

Hi @Anonymous,

Could post your table structures(including the table relationships) with some sample data and your expected result? So that we can better assist on this issue. 

Regards

[Anonymous]

Users table

person_ref	Level	Team	create_timestamp	Division	Yearly Target	CV Sent Monthly Target	Interview per month
35567	Consultant	Commerce	27/11/2014 08:58	Perm	£     150,000.00	20	12
45059	Researcher	Commerce	30/03/2016 14:20	Perm		20	12
37684	Consultant	Interim	29/06/2015 09:24	IC		20	12
54707	Researcher	Interim	10/10/2016 09:03	IC		20	12
27801	Consultant	Commerce	25/03/2013 11:44	Perm	£     200,000.00	20	12
44537	Consultant	Interim	22/02/2016 16:35	IC		20	12
36790	Researcher	Banking	04/03/2015 13:43	Perm	£     120,000.00	20	12
44930	Researcher	Interim	21/03/2016 14:26	IC		20	12
53474	Researcher	Commerce	12/08/2016 11:59	Perm		20	12
33839	Consultant	Commerce	12/09/2014 12:53	Perm	£     200,000.00	20	12
54008	Researcher	Consulting	12/09/2016 08:46	Perm		20	12
46205	Researcher	Commerce	19/05/2016 10:35	Perm		20	12
11484	Manager	Interim	23/06/2009 11:48	IC		20	12
18375	Manager	Interim	23/03/2011 09:39	IC		20	12
38586	Consultant	Consulting	05/10/2015 13:09	Perm	£     200,000.00	20	12
10002	Manager	Admin	19/10/2007 11:37		£     150,000.00	20	12
31498	Consultant	Interim	10/03/2014 14:31	IC		20	12
33619	Manager	Consulting	29/08/2014 16:43	Perm	£     150,000.00	20	12
57778	Consultant	Interim	08/03/2017 09:03	IC		20	12
23139	Consultant	Banking	01/05/2012 15:42	Perm	£     150,000.00	20	12
57744	Researcher	Admin	06/03/2017 13:15	Perm		20	12
44890	Consultant	Consulting	17/03/2016 08:36	Perm	£     200,000.00	20	12
15712	Manager	Commerce	07/07/2010 09:34	Perm	£     200,000.00	20	12
57366	Researcher	Interim	13/02/2017 08:24	IC		20	12

Activity 

Event Type	outcome	user_ref	Candidate_Ref	create_timestamp
CV sent	A3	11484	34532	12/02/2016
CV sent		37684	54422	12/02/2016
Target	A3	35567	44386	12/02/2016
Candidate call	A3	35567	44386	12/02/2016
Candidate call	A3	15712	37628	12/02/2016
CV sent		37684	30925	12/02/2016
CV sent	A3	37684	38252	12/02/2016
CV sent		37684	19192	12/02/2016
CV sent		37684	24772	12/02/2016
CV sent		37684	10448	12/02/2016

And a Date table joined one to many both filter on activity table.

Hopefully this helps. There are so many joins and tables in my relationships that showing that will be near impossible to work out from a screenshot. 

Thanks.

[v-ljerr-msft]

Hi @Anonymous,

Could you explain a little more on how the Users table and Activity table are related? It seem a little complex, and I cannot figure it out through the sample data. 

Regards

[Anonymous]

See Relationship below.

The Users table has a column with monthly target for each activity in activities table (shown in previous comment with sample data.

To list targets by months I've since added a new table that has the first date of each month with this target and joined to the dates table to manage that relationship. Again though strange results. I get 44 for CV sent target per month rather than 20? (see below)