Dynamic Targets/KPI by Quarter/Team

[pmcmonag]

I'm looking for a way to implement dynamic targets into gauge visuals based on current Quarter and Team selection on the page. I can think of 2 ways, but the favoured is not working properly and neither seem elegant enough. The Targets table would just be a manually inputted excel sheet which would contain all teams, all quarters, and all the different types of targets. Example below just has one, Sales over 1M. 

1. Create a Target Key to concat Team+Quarter Year as below (temporaily as a variable). Use a LOOKUP to return the correct target from the Targets table to the Sales table, then this can be put in the 'Target Value' for the gauge visual as an average/min/max. This works but may mean adding many targets (new calculated columns) to the Sales(fact) table. 

2.  Create a Target Key as a calculated column to concat Team+Quarter Year as below. This can then be linked to the Targets Table. This returns the right values in a data table, but the target column into the Gauge 'Target Value' is needs to do some sort of aggregation of the entire column, not the specific value. 

Any help would be great thanks - I can't seem to find a post similar.

pbix link: https://1drv.ms/u/s!ApMb66WM9jTdhG0avOvTfJAdztnU

 

[v-danhe-msft]

Hi @pmcmonag,

What is your desired result? Could you please offer me more information or post your desired result if possible?

Regards,

Daniel He

[pmcmonag]

Hi Daniel. Sorry to try to be more clear, I've tried to annotate (badly) below. 

For this filter selection (Team="North", Financial Quarter Year ="FQ2 2020"), the Target Value should be 4 (row highlighted yellow), from the TARGETS table. This pulls through to the Data Table  OK, but not in the Gauge. That is because I need to pick some sort of aggregation in the 'Target Value' for the gauge visual. 'Min' is obviously taking the minimum of the target column (red box). 

So 4 is the desired result in the instance below.

I've got to this point by using the model as below also, where North and FQ Year have been concatenated to make a Key, to create a relationship between the tables. Maybe there is a better approach? LOOKUP?

https://1drv.ms/u/s!ApMb66WM9jTdjhxPy4zQ48X6Lw_q

Thanks,

Patrick 

[v-danhe-msft · Accepted solution]

Hi @pmcmonag,

Based on my test, you could refer to below steps:

Modify the relationship between 'Sales' and 'Target Tables':

Create a measure:

MaxTarget = CALCULATE(MAX('Targets Table'[Sales over £1M Target]),ALL('Sales'))

Now you could get the correct result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Hi, 

I´m trying to build a kpi´s with the goal set on the previous quarter.  The Pbi file its based on an excel speadsheet that is filled mannualy with some issues that are being described. i have a measure that its a COUNTA fx to counts the lines of the issues to get the total issue. Those issues are being detailled by region, Model and Quarter.

My objective its to build a KPI for the total issues and when i filter it by model or region i get the goal based on the previous quarter.

Anyone can help me with this? @v-danhe-msft  do you know if this discussion can be related to the solution? 

the columns that i mentioned are like this in the excel file

YYYYQ#	Region	Model Reference	ISSUE SHORT DESCRIPTION
2018Q4	EMEA	FRANCE	Delay on UAT MPFs Delivery
2018Q4	EMEA	FRANCE	Folder structure not correct
2018Q4	EMEA	FRANCE	UAT MPFs wrong valuation date
2018Q4	EMEA	ROMANIA	Delay on UAT MPFs Delivery
2019Q1	EMEA	FRANCE	CI requirements saved in incorrect place
2019Q1	EMEA	BULGARIA	PTDT issues