Forum Discussion
Average Line in Column Chart not calculating
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
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
5 Replies
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] )
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.
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!!
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
