Skip to main content
cancel
Showing results for 
Search instead 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

Reply
SammyPub
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
cat1subcat1  1
cat1subcat1  2
cat1subcat1  3
cat1subcat2  2
cat1subcat2  3
cat1subcat3  3
cat2subcat4  1
cat2subcat4  1
cat3subcat1  3
cat3subcat1  1
cat3subcat2  1
......
catnsubcatn  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

View solution in original post

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
SammyPub
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

daxer-almighty
Solution Sage
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors