How to get Average Values at the last row in a table

[Kyung_Park]

Hello guys,

I am trying to get average values for each column at the last row like the following:

Please guide me to make it step by step!

Thank you.

[ilze.krauze]

Hi Kyung_Park,
Thanks for reaching out!

Try following formula using your existing calculations (A in the example):

CASE WHEN 
-- for total line:
  Dimensions(
    VisibleRowsSet().Item(0).item(0).Dimension.Name
  ).CurrentHierarchyMember.Name = "$total_aggregate"
THEN Avg(VisibleRowsSet(),[Measures].[A])
ELSE [Measures].[A]
END

I hope this helps.

Best,
Ilze

[Kyung_Park]

Hello @ilze.krauze

Thank you for your response.

I’ve just tried your method, but it seems like it’s not working.

After I create a new formula using your method, it shows like the following:

Testing Column is what I newly added using your formula, but it seems like it has the same values as A.

[ilze.krauze]

Hi Kyung_Park,

Add Total of rows to show the Average with this formula.

Best,
Ilze

[Kyung_Park]

Hi @ilze.krauze
Thank you so much! It works!