Forum Discussion

Iamnotarobot's avatar
Iamnotarobot
Regular Visitor
6 years ago
Solved

add average per category lines to chart

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 meas...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601