Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

multiple filters

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.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-juanli-msft
        Community Support
        6 years ago

        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.