View those with a Minimum of 2 classes

[benishiryo]

hi guys. my table is called Consol. i want to see the average ratings only when the trainer has >=2 classes. that is determined by how many times there is a number in Total Pax. so if my table is as such:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Trainer[/TD]
[TD]Category[/TD]
[TD]Total Pax[/TD]
[TD]Rating[/TD]
[/TR]
[TR]
[TD]Remy[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Remy[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Remy[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]A[/TD]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]A[/TD]
[TD]12[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Daryl[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Daryl[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Daryl[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]




I have a Slicer for category. so i need to be able to see the average rating still appear if i click on A/B/C, although Total Pax is parked in A. hence, only De Jong should appear as he have 2 classes. how do i type the Measure? So the answer should be an average of the numbers in red if none of the slicers is selected. if B is selected, it should be (2 + 3) / 2.

ps: this is my attempt at it
RtgGE3:=CALCULATE([Ave Rating],ALL('Consol'[Category]),FILTER(ALL('Consol'[Category]),COUNTA([Total Pax])>=3))

Ave Rating is a simple:
Ave Rating:=AVERAGE([Rating])

 

[RoryA]

I am certain there will be better ways, as I've only just started learning DAX, but for a quick and dirty way:

Add a calculated column called PAXCount using: =COUNTROWS(filter(consol,[Trainer]=EARLIER([Trainer])&&[Total Pax]<>BLANK()))

Then add the following measures:
[Ave Rating] = average(Consol[Rating])
[New average] = =CALCULATE([Ave Rating],Consol[PAXCount]>1)

 

[benishiryo]

thanks Rory! will be interested to add in straight into the measure, but i'm all good with this method

 

[benishiryo]

what if i need it a little more complex? say course X+Y >=2 or Z >=2 added to it. this means only De Jong's Course of X + Y will be shown
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Trainer[/TD]
[TD]Course[/TD]
[TD]Category[/TD]
[TD]Total Pax[/TD]
[TD]Rating[/TD]
[/TR]
[TR]
[TD]Remy[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Remy[/TD]
[TD]X[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Remy[/TD]
[TD]X[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]X[/TD]
[TD]A[/TD]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]X[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]X[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]Y[/TD]
[TD]A[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]Y[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]Y[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]Z[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]Z[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]De Jong[/TD]
[TD]Z[/TD]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]