Dynamic Gauge Targets based on Slicers

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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.

[v-juanli-msft]

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]

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