topic Re: Average of category using only subcategories with more than x rows in DAX Commands and Tips

Average of category using only subcategories with more than x rows

SammyPub — Sat, 24 Apr 2021 22:28:12 GMT

Hi, I want to get the average for each category, using only the subcategory rows where there are more than x rows. The number x is defined by a slicer.

Example, considering the following table, the calculation of each category average should ignore the red lines because those subcategories have less than 2 rows (in case of the slicer equals to 2)

How can i make it?
Thanks

Category	SubCategory	Value
cat1	subcat1	1
cat1	subcat1	2
cat1	subcat1	3
cat1	subcat2	2
cat1	subcat2	3
cat1	subcat3	3
cat2	subcat4	1
cat2	subcat4	1
cat3	subcat1	3
cat3	subcat1	1
cat3	subcat2	1
..	..	..
catn	subcatn	3

The average should be:

cat1 = (1+2+3+2+3)/5,5

cat2 = (1+1)/2=1

cat2=(3+1)/2=2

Re: Average of category using only subcategories with more than x rows

daxer-almighty — Sat, 24 Apr 2021 23:26:42 GMT

Of course it's possible but without knowing your model and whether it's to be a visual average or not... it's not possible to give you a correct formula.

Re: Average of category using only subcategories with more than x rows

SammyPub — Sun, 25 Apr 2021 09:49:22 GMT

Ok, I'm sending here the link for the pbix file: Sample

How can I reflect the changes on the slicer to table 2. It work fine for table 1.

On table 2, what I want is to calculate the averave using only the schools with more than x exams. The table is calculating the average for the regions with more than x exams.

Thanks

Re: Average of category using only subcategories with more than x rows

Anonymous — Mon, 26 Apr 2021 05:08:51 GMT

// If only one category has been selected, // the measure returns the average of Value // across all subcategories of the category // for which the number of rows is >= the // selected number from the slicer. If many // categories are seleced, it returns the // average of the averages as calculated above // for each individual category. [Category Average] = var vMaxNumOfRowsAmongAllSubcategories = CALCULATE( MAXX( SUMMARIZE( T, T[Category], T[Subcategory] ), CALCULATE( COUNTROWS( T ) ) ) ALL( ) ) var vMinNumOfRowsInSubcategories = SELECTEDVALUE( Slicer[X], vMaxNumOfRowsAmongAllSubcategories + 1 ) var vCatSubcatCombinationsOfInterest = FILTER( SUMMARIZE( T, T[Category], T[Subcategory] ), var vRowCount = CALCULATE( COUNTROWS( T ) ) return vRowCount >= vMinNumOfRowsInSubcategories ) var vResult = CALCULATE( AVERAGEX( DISTINCT( T[Category] ), CALCULATE( AVERAGE( T[Value] ) ) ), vCatSubcatCombinationsOfInterest ) RETURN vResult