multiple filters

[Anonymous]

I'm trying to create a report that will show the average number of enrollments in classes, along with the total number of times the classes have run. It's showing the highest average enrollment on a line, with the total number of class runs set as columns. I want to keep the highest average (top 15) enrollment, but when I try to limit the class runs to show only those classes that have run more than 5 times, it only shows me the classes of the top 15 average enrollments (in other words, it only shows 3 classes). I would like to see the highest average enrollment of classes that have run at least 5 times.

[v-juanli-msft]

Hi @Anonymous 

Could you share some example data for analysis?

Based on my understanding, you want to show only those classes that have run more than 5 times, also these classes's average enrollments is included in the highest average (top 15) enrollment.

Right?

Best Regards
Maggie

[Anonymous]

Alas, it's proprietary information, so I really can't share anything.

But yes. It's a line/column chart. Classes are in the Shared Axis, the Count of Classes is the line value and the average enrollment is in the column value. Only looking for average enrollment for classes that have run at least 5 times.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Assume you have data example below

Create measures to calculate the average enrollment and run times for every class.

average = CALCULATE(AVERAGE(Sheet3[enrollment]),FILTER(ALLSELECTED(Sheet3),Sheet3[class]=MAX(Sheet3[class])))

run times = CALCULATE(DISTINCTCOUNT(Sheet3[run time]),FILTER(ALLSELECTED(Sheet3),Sheet3[class]=MAX(Sheet3[class])))

Based on my understanding, you want to show top n average enrollment but at the same time it's run times >=5,

If so, create measures

rank =
IF (
    [run times] >= 5,
    RANKX (
        FILTER ( ALL ( Sheet3[class] ), [run times] >= 5 ),
        [average],
        ,
        DESC,
        DENSE
    ),
    0
)

topn&&>5times = IF([rank]<=[top n Value]&&[rank]<>0,[average],BLANK())

>5 run times = IF([run times]>=5,[run times])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.