Forum Discussion

tobiasmcbride's avatar
tobiasmcbride
Icon for Helper III rankHelper III
6 years ago
Solved

Formatting chart visual and slicers

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.

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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.

5 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      tobiasmcbride
      Icon for Helper III rankHelper III
      6 years ago

      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?

      • MFelix's avatar
        MFelix
        Icon for Super User rankSuper User
        6 years ago

        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