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

 

 

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.

5 Replies

  • 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.
  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    3 years ago

    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's avatar
      Mikelytics
      Resident Rockstar
      3 years ago

      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