Line Chart: Show total average as default. Show specific categories on filter selection

[danialsj]

I have a line chart that shows the average score of 4 regions over the last 6 months.

I want the line chart to show the total average of all 4 regions in one line when there is no filter selected.

When Region 1 is selected, the line chart should show only Region 1's average line. When both Region 1 and 2 are selected, the line chart should show both Region 1 and 2's average in two lines.

Is this possible to do?

Thanks.

[MFelix · Accepted solution]

Hi @danialsj ,

Being a line chart you need to create 5 measures (one for each region and one for the average):

Region 1 =
IF (
    DISTINCTCOUNT ( 'Table'[Region] )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Region] ); ALL ( 'Table'[Region] ) );
    BLANK ();
    IF (
        CONTAINS ( 'Table'; 'Table'[Region]; 1 );
        CALCULATE ( AVERAGE ( 'Table'[Value] ); 'Table'[Region] = 1 )
    )
)

Region 2 =
IF (
    DISTINCTCOUNT ( 'Table'[Region] )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Region] ); ALL ( 'Table'[Region] ) );
    BLANK ();
    IF (
        CONTAINS ( 'Table'; 'Table'[Region]; 2);
        CALCULATE ( AVERAGE ( 'Table'[Value] ); 'Table'[Region] = 2 )
    )
)

Region 3 =
IF (
    DISTINCTCOUNT ( 'Table'[Region] )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Region] ); ALL ( 'Table'[Region] ) );
    BLANK ();
    IF (
        CONTAINS ( 'Table'; 'Table'[Region]; 3 );
        CALCULATE ( AVERAGE ( 'Table'[Value] ); 'Table'[Region] = 3 )
    )
)

Region 4 =
IF (
    DISTINCTCOUNT ( 'Table'[Region] )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Region] ); ALL ( 'Table'[Region] ) );
    BLANK ();
    IF (
        CONTAINS ( 'Table'; 'Table'[Region]; 4 );
        CALCULATE ( AVERAGE ( 'Table'[Value] ); 'Table'[Region] = 4 )
    )
)

Region All =
IF (
    DISTINCTCOUNT ( 'Table'[Region] )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Region] ); ALL ( 'Table'[Region] ) );
    CALCULATE ( AVERAGE ( 'Table'[Value] ) );
    BLANK ()
)

Then just place the measures in the chart.

Regards,

MFelix