Forum Discussion
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 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!
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.
3 Replies
- Icey
Community Support
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
Super User
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 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 🙂
