Formatting chart visual and slicers

[tobiasmcbride]

I have a chart visual which is displaying interlinked data on actual vs budgeted headcount. The headcount and latest budget visual displays perfectly for the dates.

However, I have some slicers on the report that I can apply to the current headcount to give me an idea of trends over time (i.e., how many at a particular pay grade, team, location etc.). This filters the actual data quite nicely but was wondering if, when I select these slicers if there was a way to remove the budget line from the visual as the budget does not have the same level of granularity as the actuals data. 

When I select the visual slicers it does correctly filtert actuals but budget stays on the graph and because it is a total figure is very high and distorts the chart visual for the actuals data. 

Any advice on filtering this or working out a way to display both would be fantastic.

[MFelix]

Hi @tobiasmcbride ,

Depending on the slicers you are selecting you may need to adjust your budget measure with something similar to this:

Budget = IF (DistinctCount(Table[SlicerColumn]) = DistinctCount(ALL(Table[SlicerColumn])) ; [BudgetMeasure]; Blanks())

So basically you are checking if all the options on the slicer are being selected if yes the value return nothing otherwise returns the budget. You can add additional parameters if you have more than 1 slicer.

[tobiasmcbride]

Hi @MFelix 

I cannot seem to use that formula as it suggests it only accepts a column reference as an argument - i don't think it's allowing the "ALL" function?

[tobiasmcbride]

And in addition @MFelix how can I add in additional categories/sliders into the formula?

[MFelix · Accepted solution]

Hi @tobiasmcbride ,

You can create something similar to this:

Measure =
VAR slicer1 =
    IF (
        DISTINCTCOUNT ( 'Table (2)'[SlicerColumn1] )
            = CALCULATE (
                DISTINCTCOUNT ( 'Table (2)'[SlicerColumn1] );
                ALL ( 'Table (2)'[SlicerColumn1] )
            );
        1;
        0
    )
VAR slicer2 =
    IF (
        DISTINCTCOUNT ( 'Table (2)'[SlicerColumn2] )
            = CALCULATE (
                DISTINCTCOUNT ( 'Table (2)'[SlicerColumn2] );
                ALL ( 'Table (2)'[SlicerColumn2] )
            );
        1;
        0
    )
RETURN
    IF ( slicer1 + slicer2 = 1; blank; [Budget])

Add a slicer setting per each slicer and keep adding +  for each variable.

[MFelix]

Hi @tobiasmcbride ,

My error the formula should be:

Budget = IF (DistinctCount(Table[SlicerColumn]) = CALCULATE (DistinctCount(Table[SlicerColumn]);ALL(Table[SlicerColumn]))) ; [BudgetMeasure]; Blanks())

Sorry for the mistake