Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Matrix - Average from Previous level

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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.