adding an average measure to a chart with existing column variables

[MartynasBI]

Hi,

I have the following area chart visual, which contains a legend of two variables (CLO A and CLO B).

I would like to calculate the average value of the CLO A and CLO B, and add that average to the area chart as the "average CLO" - which would be a third area line in the chart.

Here is the data used

Many thanks in advance

[kpost]

Does this work?

AVERAGE_BY_REVENUE% =
CALCULATE(

       AVERAGEX(

              VALUES(Table[Revenue Growth']),

              COALESCE(SUM(Table['Nr of companies']), 0)

                        ), 
ALL(Table[CLO Name])
)

//Mediocre Power BI Advice, but it's free//

[kpost]

Does this work?  (The paren

AVERAGE_BY_REVENUE% =
CALCULATE(

       AVERAGEX(

              VALUES(Table[Revenue Growth']),

              COALESCE(SUM(Table['Nr of companies']), 0)

                        ), 
ALL(Table[CLO Name])
)

//Mediocre Power BI Advice, but it's free//

[MartynasBI]

Hi @kpost 

Unfortunately it did not work

[v-zhangti · Accepted solution]

Hi, @MartynasBI 

You can try the following methods.

Column:

Average = AVERAGEX(FILTER('Table',[Revenue growth]=EARLIER('Table'[Revenue growth])),[Nr of companies])

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[MartynasBI]

Hi @v-zhangti ,

Yes, the numbers are correct, thanks!

However, I Power BI does not allow me to add this newly created column to the area chart visual Y axis. Do you know any workarounds around that?

Thanks,

[kpost]

It would be a measure, not a column.

[MartynasBI]

Unfortunately a measure in this case is not sufficient, as it cannot be added to the visual