Average Line in Column Chart not calculating

[hackfifi]

Good Day - I am strugging to get an STRAIGHT Average Line for a measure irrespective of the X axis.

I have a measure which calculates the average correctly i.e. shown in the image/card as 2.03

When i add the measure as a line, it reverts to the data based on the X axis. I just want the 2.03 to be a straight line across the graph for the selected data. Kindly note i have selected 3 projects below; but i have a dropdown with 100+ projects.

Avg PF = CALCULATE(AVERAGEX(Projects,[PF]),ALLSELECTED(Projects[Project]))

Thanks

[amitchandak]

@hackfifi , Use

Avg PF = CALCULATE(AVERAGEX(Projects,[PF]),ALLSELECTED(Projects))

[hackfifi]

On more query, if i had ANOTHER TABLE "Exclude Projects" with data values as Project 1, Project 2, Project 3 etc which is NOT related to MASTER PROJECT TABLE, is possible to calculate the average excluding the selected projects from "Exclude Projects"

For example, if i select "Project 3" from "Exclude Projects" slicer, i would like the average line to show only average of Project 1 & Project 2.

Thanks mate!!

[v-lili6-msft · Accepted solution]

HI  @hackfifi 

Just add a IF conditional in formula as below:

Avg PF = 
IF(NOT(VALUES(Projects[Project])) IN VALUES('Exclude Projects'[Project]), 
CALCULATE(AVERAGEX(Projects,[PF]),ALLSELECTED(Projects)))

Result:

Regards,

Lin

[danextian]

Hi @hackfifi ,

I am not quite sure what you are trying to accomplish. Are you trying to get the average for each project (as there is only project 1 in your x axis) or the average for all projects? Insted of  ALLSELECTED ( Projects[Project] ), you could try:

• ALLEXCEPT( Projects, Projects[Project] )
• or ALL ( Projects[Project] )

[hackfifi]

Sorry Mate - it is actually Project 1, Project 2, Project 3 etc.

I would like to get AVERAGE of ALL Projects. The measure works as shows in the DATA LABEL as 2.03, but when i add that AVERAGE measure to chart, it calculates by project.