Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create Dual Axis Chart with few modifications

What to create Dual Axis chart - With a filter Showing data for Just one year. Columns Showing the data for # Touched - For the Current Year as well as Prior Year i.e. if we select 2018 using filter...
Need Help
  • MFelix's avatar
    MFelix
    6 years ago

    Hi Anonymous ,

     

    Create the following 4 measures:

     

    # Touched current year =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
)

# Touched PY =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
)


% Eligible =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
)
    / CALCULATE (
        SUM ( 'Table'[Orig_Qualified] );
        FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
    )

% Eligible PY =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
)
    / CALCULATE (
        SUM ( 'Table'[Orig_Qualified] );
        FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
    )

    Now place the first two measures on columns and the last two measures on line.

     

    See attach on PBIX file attach.

     

    Regards,

    MFelix

MFelix's avatar
MFelix
Super User
6 years ago

Hi Anonymous ,

 

Create the following 4 measures:

 

# Touched current year =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
)

# Touched PY =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
)


% Eligible =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
)
    / CALCULATE (
        SUM ( 'Table'[Orig_Qualified] );
        FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) )
    )

% Eligible PY =
CALCULATE (
    SUM ( 'Table'[Touched] );
    FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
)
    / CALCULATE (
        SUM ( 'Table'[Orig_Qualified] );
        FILTER ( ALL ( 'Table'[Year] ); 'Table'[Year] = MAX ( 'Table'[Year] ) - 1 )
    )

Now place the first two measures on columns and the last two measures on line.

 

See attach on PBIX file attach.

 

Regards,

MFelix

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi MFelix , Thank you so much. You hit the right chord in 1st attempt.

Regards,

Sushant