average horizontal line when analytics not available

[Anonymous]

Hi

on "Line and clustered columns chart" analytics pen is not available.

Lats say Table1 has ID, Date, PersonName, Month (calculated from Date) and Date_PersonName (combind col for join)

Table2 has Date, PersonName, Hours and Date_PersonName (combind col for join)

I have a 'Line and clustered columns chart' with

shared axis: PersonName

column series: month
column values: capacity = DIVIDE(DISTINCTCOUNT('Table1'[ID]), SUMX('Table2','Table2'[Hours]))

Line values:  AvgCapacity = AVERAGEX('Table1', DIVIDE( DISTINCTCOUNT('Table1'[ID]), SUM('Table2'[Hours])))

so far so good, for each person i get columns for each month and one AvgCapacity  line that is the average for each person.

now i want to add one horizontal line that is the average of all people and all months that are on the chart (considering other slicers on the page)

In analytics pen you can do that but for this chart analytics is not available

i tried some combinations but each time i either get the same as AvgCapacity  or just a 0 line...

how could that be achieved?

Thanks

[v-chuncz-msft]

@Anonymous ,

You may try adding ALLSELECTED.

https://community.powerbi.com/t5/Desktop/RANK-MIN-OF-SUM-grouped-by-two-fields/m-p/581289#M275025

[Anonymous]

thank you @v-chuncz-msft !

can you please elaborate?

My average line across the chart is  AVERAGEX('Table1', DIVIDE( DISTINCTCOUNT('Table1'[ID]), SUM('Table2'[Hours])))

If i add AVERAGEX(ALLSELECTED('Table1'), DIVIDE( DISTINCTCOUNT('Table1'[ID]), SUM('Table2'[Hours])))

I get the same line. is that what you meant?

What I want is to add one horizontal straight line that is the average capacity of all data in the chart (not by column)

I think it is the same as adding "Average Line" on the Analytics pane but Analytics pane is not available for 'Line and clustered columns chart'