add average per category lines to chart

[Iamnotarobot]

Hi everyone, 

Is there a way to add average lines per category to line charts? 

I know you can add constant lines and an average line, but I want the average per category. Can you put in a measure for the constant line somehow? Or can another visual achieve this?

The output of what I want is in my dodgy paint image



Thanks!

[Iamnotarobot]

Thanks both, that helps me get the average for each category. Unfortunately looks like I can't get it on a line chart as I hoped though, so might have to go the column line chart approach suggested. Thanks 🙂 

[Icey · Accepted solution]

Hi @Iamnotarobot ,

What about this?

Avg Bob =
CALCULATE (
    AVERAGE ( 'Table'[Quantity] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Name] = "Bob" )
)

Avg John =
CALCULATE (
    AVERAGE ( 'Table'[Quantity] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Name] = "John" )
)

Avg Mia =
CALCULATE (
    AVERAGE ( 'Table'[Quantity] ),
    FILTER ( ALL ( 'Table' ), 'Table'[Name] = "Mia" )
)

Best Regards,

Icey

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

[amitchandak]

You can create category Average using Quick formula.

Refer

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Only one formula you have to use. You need to use ALLEXCEPT 

Average of available =
CALCULATE (
    AVERAGE ( 'SUM of AVERAGE'[Value] ),
    FILTER (
        ALLEXCEPT ( 'SUM of AVERAGE', 'SUM of AVERAGE'[City] ),
        'SUM of AVERAGE'[Category] = "AVAILABLE"
    )
)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601