Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
miguesnet
Frequent Visitor

Graph : Lines per category and Average line overall

Hi, 

 

Is there a way to add automatically on average line per month on a graph?

I have in this example a cost per site per month (5  sites )  and i would like to add a line with the average monthly costPower Bi _ cost per site per month.png

Thanks in advance for your help

 

1 ACCEPTED SOLUTION

Hi @miguesnet ,

 

I have created a sample for your reference, please check the following steps as below.

 

1. Create a calculated table as below and make it related to our fact table.

Table 2 = UNION(VALUES('Table (2)'[category]),{"avg"})

2. Then we can achieve that by a measure.

Measure = 
IF (
    MAX ( 'Table 2'[category] ) = "avg",
    CALCULATE (
        AVERAGE ( 'Table (2)'[value] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[date] )
    ),
    SUM ( 'Table (2)'[value] )
)

2.PNG

 

For more details, please check the pbix as attached.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@miguesnet 

You can have like using Analytics . But in this case you need a measure like

 

Calculate([Measure],All(Site[Site]))

 

Also refer :https://docs.microsoft.com/en-us/power-bi/desktop-analytics-pane

 

 if you need more help make me @

Appreciate your Kudos.

 

 Hi,

 

Thanks for your answer.

I created a new measure as you describe but i can not combine both measures on the same graph. It must be possible

Power Bi _ cost avgeper month.pngPower Bi _ cost per site per month.png

 

Hi @miguesnet ,

 

I have created a sample for your reference, please check the following steps as below.

 

1. Create a calculated table as below and make it related to our fact table.

Table 2 = UNION(VALUES('Table (2)'[category]),{"avg"})

2. Then we can achieve that by a measure.

Measure = 
IF (
    MAX ( 'Table 2'[category] ) = "avg",
    CALCULATE (
        AVERAGE ( 'Table (2)'[value] ),
        ALLEXCEPT ( 'Table (2)', 'Table (2)'[date] )
    ),
    SUM ( 'Table (2)'[value] )
)

2.PNG

 

For more details, please check the pbix as attached.

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

March Release has a multi-axis line chart. And I remember that bar -line was only splitting bar. Check and try

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-march-2020-feature-summary/

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.