Forum Discussion

danialsj's avatar
danialsj
Frequent Visitor
6 years ago
Solved

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

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.

 

 

Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    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

     

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    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