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.
v-easonf-msft's avatar
v-easonf-msft
Community Support
3 years ago

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'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.