Fixed average regardless of filters

[jsbourni]

Hi,

I'm playing with school data like this:

Year	Semester	Cycle	Department	Course	Enrolled
2018-2019	Winter	1	1	X123	6
2018-2019	Fall	1	1	X234	12
2018-2019	Summer	1	1	X345	34
2018-2019	Winter	1	1	X456	45
2018-2019	Fall	1	1	X567	23
2018-2019	Summer	1	1	X678	32
2018-2019	Winter	1	1	X789	21
2018-2019	Fall	1	1	X900	16
2018-2019	Summer	2	1	X1011	33
2018-2019	Winter	2	1	X1122	26
2018-2019	Fall	2	1	X1233	24
2018-2019	Summer	2	1	X1344	12
2018-2019	Winter	2	1	X1455	12
2018-2020	Fall	2	1	X1566	30
2018-2021	Summer	2	1	X1677	23

I'm trying to set a fixed average per cycle per year to compare the average number of student enrolled per course per year to the grand mean per cycle and, ultimately, count the number of courses per department or by cycle (one visualisation each) that are under the (rounddown)mean per cycle.

The problem I have is that I can't set the fixed value for the cycle mean, it is always dependant on the variables in the visualisations. One thing I should mention is that some filters have to be added to the visualisations as not all the data should be used to do the calculations. Hence the alternate idea of creating a table with dynamically-set means per cycle.

The desired output should (I think) look like this:

year	Cycle	Department	Mean per cycle	Nb of courses under
2018-2019	1	1	23.6	4 or 50%
2018-2019	2	1	22.9	2 or 29%

This would allow to create a visualisation by cycle and another by department (using small multiples).

I created a measure for the mean per cycle, but if I add the department in the visualisation, everything changes:

Cycle mean = CALCULATE(AVERAGE(DATA[Enrolled]), DATA[Credits]<>0, DATA[Enrolled]<>0)

Givent that, I tried to calculate the number of courses under the mean but did not come close to anything, most of the time it returns blank cells.

I tried to do this all at once in a single measure, but maybe this is not the appropriate way.

Would take any advice. Thanks,

[jdbuchanan71 · Accepted solution]

@jsbourni 

You can use ALLEXCEPT to remove filters from a calcualtion so it looks at more data than just the rows it is on.

Cycle Mean = 
CALCULATE (
    AVERAGE ( DATA[Enrolled] ),
    DATA[Enrolled] <> 0,
    ALLEXCEPT ( DATA, DATA[Year], DATA[Cycle] )
)

Then we can use that measure to count the courses that are under on Enrolled.

Courses Under = 
COUNTROWS(
    FILTER(DATA,DATA[Enrolled]<[Cycle Mean])
)

I have attached my sample file for you to look at.

[jsbourni]

That is very close, thanks. I don't know why, the addition of Department on my side (in my pbix) still changes the cycle mean.

If I wanted the proportion of courses under the mean, should I wrap your measure in a DIVIDE formula?

Thank you again

EDIT: works like a charm, my bad