Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |