Average on average aggregation

[AshwiniBende]

Hi All, 

I am trying to do the average of "Time Spent on Page" by Session ID. Once this is done, I need to show the overall average. Here is an example. All I need to report on my dashboard is 3.6 minutes. I am very new to PBI, any insights you could provide is greatly appreciated. Fyi, I do not need to create that second table at all, I need to work off of my original data only and need to report on overall average which is 3.6

[JGARROD]

Hi, i was wondering how you would do this with a 3rd level, so ive managed to relicate as mentioned above but i need to do the following:

Level 1 figures (averaged = Level 2) > Level 2 figures (averaged = Level 3) > Level 3 averaged figures gives overall score - how would i acomplish this?

It seems by following this method i would need to nestle a VAR table within a VAR table? any help would be much appreciated. 

[Jihwan_Kim · Accepted solution]

Hi,

please check the below picture and the attached pbix file.

Result measure: =
VAR newtable =
ADDCOLUMNS (
SUMMARIZE ( Data, Data[Session ID] ),
"@avgpersession", CALCULATE ( AVERAGE ( Data[Minute duration] ) )
)
RETURN
AVERAGEX ( newtable, [@avgpersession] )

[AshwiniBende]

Quick follow up on this. I am getting the below error when I used the same formula with in a report where DirectQuery is used instead of import. Is there any workaround for this?

[AshwiniBende]

Thank you so much for your quick response. It worked.

[Richard_100]

Hello

This should work:

[AVG of AVG] =

VAR _Prep = SUMMARIZE(Table1, Table1[Session ID], "@AVG", AVERAGE(Table1[Overall Session Duration (Minutes)]))

RETURN AVERAGEX(_Prep, [@AVG])

Where Table1 is the name of your original data table.  _Prep is a variable that acts as the equivilent of your table from the right of your screenshot, providing the average for the Session ID in your visual; the AVERAGEX then averages over that

Hope that helps. 

Regards

Richard

Regards

Richard

[AshwiniBende]

Thank you