cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Average of category using only subcategories with more than x rows

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable
``````// 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``````
3 REPLIES 3
Anonymous
Not applicable
``````// 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``````
Frequent Visitor

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

Solution Sage

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.