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