Matrix - Average from Previous level

[Anonymous]

Hi,

I have a table with 366 rows.

I'm trying to calculate the average in a matrix, but I want the average to be calculated from the level below.

For example, currently the average for “Queens” is calculated using all the 336.

I want to calculate the average for Queens using the 5 fruits’ averages shown below.

The average would be found using ( 99.6323, 99.6334, 99.5594, 99.5856, 99.6368) = 99.6095

Does anyone know how to calculate the average using the numbers from the previous level in a matrix?

[Mikelytics]

Hi @Anonymous 

Please try

Average over Fruits =

AVERAGEX(
   VALUES(Table[Fruits]),
   CALCULATE(AVERAGE(Table[Fruits]))
)

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[Mikelytics]

Hi @Anonymous

Did you try my formula? I made a small mistake and adjusted it and it should work. Can you please try and show what happens?

Average over Fruits =

AVERAGEX(
   VALUES(Table[Fruits]),
   CALCULATE(AVERAGE(Table[Data]))
)

Best regards

Michael

-----------------------------------------------------

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!

@ me in replies or I'll lose your thread.

-----------------------------------------------------

LinkedIn

[v-easonf-msft]

Hi, @Anonymous 

Please try measure formula like:

Average for Queens = 
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Team],
        'Table'[Country],
        'Table'[State],
        'Table'[County],
        'Table'[Fruit],
        "Avg", AVERAGE ( 'Table'[Data] )
    )
RETURN
    AVERAGEX ( tab, [Avg] )

Result = 
IF (
    ISINSCOPE ( 'Table'[County] ) && NOT ISINSCOPE ( 'Table'[Fruit] ),
    [Average for Queens],
    AVERAGE ( 'Table'[Data] )
)

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi Eason,

Thanks for the ISINSCOPE suggestion and PBIx file. It was helpful.
Your solution does work however my example was too simple. I need to apply the prior level idea to all levels. I tried to create an average table for each level but don't know how to create a nesting formula to use the prior levels' averages. For example, to find the Team average using the Country Average derived from the State Average derived from the County Average.
Any thoughts?  I have the latest file of my work but don't see an option to attach the file.

[Anonymous · Accepted solution]

I think I got it to work. I calculated the average at each level. Here is an example of the average at the highest level.

_a1 =

VAR L5 =

    SUMMARIZE (

        'Table',

        'Table'[Team],

        'Table'[Country],

        'Table'[State],

        'Table'[County],

        'Table'[Fruit],

        "Avg", AVERAGE ( 'Table'[Data] )

    )

VAR L4 = GROUPBY(L5,[Team],[Country],[State],[County],"Avg", AVERAGEX(CURRENTGROUP(),[Avg]))

VAR L3 = GROUPBY(L4,[Team],[Country],[State],"Avg", AVERAGEX(CURRENTGROUP(),[Avg]))

VAR L2 = GROUPBY(L3,[Team],[Country],"Avg", AVERAGEX(CURRENTGROUP(),[Avg]))

VAR L1 = GROUPBY(L2,[Team],"Avg", AVERAGEX(CURRENTGROUP(),[Avg]))

RETURN

    AVERAGEX ( L1, [Avg] )

Here is the logic for the final measure.

_Logic2 =

IF (

    ISINSCOPE ( 'Table'[County] ),

    [_a4]

,IF (

    ISINSCOPE ( 'Table'[State] ),

    [_a3]

,IF (

    ISINSCOPE ( 'Table'[Country] ),

    [_a2]

,IF (

    ISINSCOPE ( 'Table'[Team] ),

    [_a1]

 //Else  

,AVERAGE ( 'Table'[Data] )

)

)))

Seems to work.