Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Gauge Targets based on Slicers

I'm just getting my hands dirty with Power BI and it is both amzaing and overwhelming.  I've been digging around on the forums for a while and can't find an answer to this question so please forgive if I have missed it somewhere.

 

I'm building a marketing dashboard with individual gauges to show the total number of new names per week, month, and year.  The page has slicers to allow the selection of multiple verticals.  I'd like the Target of the gauge to reflect the associated vertical's target or the sum of the multiple verticles' targets if they picked more than one.  Those target values are in a related table so each lead record has a value reflecting the vertical target for the weekly, monthly, and yearly goal.  Showing the values on the gauge based on the slicer selection is pretty straightforward.  The targets, however are another story.  Here is a visual that hopefully helps clarify the goal.

 

 

 

 

 

 

 

 

 

 

 

 

 

Please and Thank You!

Rich

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

    Hi Anonymous 

    1.Create two tables

    new Table 1 = VALUES('Vertical Table'[Vertical])

    new Table 2 by entering data to create a table

    dimension
    yearly
    monthly
    weekly

     

    Don't create relationship for these two new tables

     

    2.Add [Vertical] from new Table 1 in the slicer, [dimension] from new Table 2 in the slicer

     

    3. Create measures in Vertical Table

    selected = CONCATENATEX(ALLSELECTED('new Table 1'),'new Table 1'[Vertical],",")

if find = FIND(MAX('Vertical Table'[Vertical]),[selected],1,0)

flag = IF([if find]>0,1,0)

target =
VAR week =
    CALCULATE (
        SUM ( 'Vertical Table'[Goal_Week] ),
        FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
    )
VAR month =
    CALCULATE (
        SUM ( 'Vertical Table'[Goal_Month] ),
        FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
    )
VAR year =
    CALCULATE (
        SUM ( 'Vertical Table'[Goal_Year] ),
        FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'new Table 2'[dimension] ),
        "weekly", week,
        "monthly", month,
        "yearly", year
    )

count =
CALCULATE (
    DISTINCTCOUNT ( 'Lead Table'[Name] ),
    FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
)

min = 0

max = [target]*1.25

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous 

    Maximum=goal from vertical table +25%

    so Maximum for selection A is 10+25%

    Why is 13 on your expected table?

    It is round up to 13, right?

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Maggie,

       

      Yes indeed.  We're counting people so it rounds up.  I'm not as concerned about the Maximum as once the Target is set I can copy it and add *1.25 to the expression.

       

      Thanks,

      Rich

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Just wanted to bump this again to see if anyone out there has an idea.  I can't be the first person who has tried to do something like this.  I am also open to other approaches to solving this issue.  One way would be to create a dashboard for each vertical and another for the consolidated number but navigating 4 dashboards seems like it defeats the purpose of a BI tool.  Are they any other visualizations someone could reccommend? 

     

    Thanks,

    Rich

    • v-juanli-msft's avatar
      v-juanli-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      Hi Anonymous 

      1.Create two tables

      new Table 1 = VALUES('Vertical Table'[Vertical])

      new Table 2 by entering data to create a table

      dimension
      yearly
      monthly
      weekly

       

      Don't create relationship for these two new tables

       

      2.Add [Vertical] from new Table 1 in the slicer, [dimension] from new Table 2 in the slicer

       

      3. Create measures in Vertical Table

      selected = CONCATENATEX(ALLSELECTED('new Table 1'),'new Table 1'[Vertical],",")

if find = FIND(MAX('Vertical Table'[Vertical]),[selected],1,0)

flag = IF([if find]>0,1,0)

target =
VAR week =
    CALCULATE (
        SUM ( 'Vertical Table'[Goal_Week] ),
        FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
    )
VAR month =
    CALCULATE (
        SUM ( 'Vertical Table'[Goal_Month] ),
        FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
    )
VAR year =
    CALCULATE (
        SUM ( 'Vertical Table'[Goal_Year] ),
        FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
    )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'new Table 2'[dimension] ),
        "weekly", week,
        "monthly", month,
        "yearly", year
    )

count =
CALCULATE (
    DISTINCTCOUNT ( 'Lead Table'[Name] ),
    FILTER ( ALLSELECTED ( 'Vertical Table' ), [flag] = 1 )
)

min = 0

max = [target]*1.25

      Best Regards
      Maggie

       

      Community Support Team _ Maggie Li
      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.