Graph : Lines per category and Average line overall

[miguesnet]

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 cost

Thanks in advance for your help

[amitchandak]

@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.

[miguesnet]

 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

[v-frfei-msft · 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] )
)

For more details, please check the pbix as attached.

[amitchandak]

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/