Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create Average Line per year in Combined Line and Stacked Column Chart

Hi,   I want to create an average line which shows different value per year in a combined line and stacked column chart. The visualization will be like below chart. The table is "Notification". The...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Please try the following formula to create a measure:

    line =
SWITCH (
    YEAR ( MAX ( 'Table'[Date] ) ),
    2018,
        CALCULATE (
            AVERAGE ( 'Table'[Notif Count] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date].[Year] = 2018 )
        ),
    2019,
        CALCULATE (
            AVERAGE ( 'Table'[Notif Count] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date].[Year] = 2019 )
        ),
    2020,
        CALCULATE (
            AVERAGE ( 'Table'[Notif Count] ),
            FILTER ( ALL ( 'Table' ), 'Table'[Date].[Year] = 2020 )
        )
)

    Open Shapes--> "Stepped" button, the final output is shown below:

     

    Here is the pbix file.

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Anonymous , Create measure like

calculate(Average(Table[Value]), allexcept(Table, Table[Year]))

or

calculate(Average(Table[Value]),filter(allselected(Table), Table[year] =max(Table[Year])))

calculate(Average(Table[Value]), removefilters([Decision Status]))