Forum Discussion

pmcmonag's avatar
pmcmonag
Advocate IV
7 years ago
Solved

Dynamic Targets/KPI by Quarter/Team

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

 

 

 

 

Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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

4 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    7 years ago

    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's avatar
      pmcmonag
      Advocate IV
      7 years ago

      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's avatar
        v-danhe-msft
        Microsoft Employee
        7 years ago

        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