Average of Average Measure in Cross tab report

[kabdelkarim]

Hi all:

Apologies if this was answered somewhere else as I could not find it, but I have the following cross tab report coming from a table called user_details with two measures (Student Average and Teachers Average), each are based on simple equation of loging counts / Total count:

Category Name          Students Average          Teachers Average

Category1                   23%                               15%

Category2                   70%                                87%

Category3                   97%                                81%

I want to get the grand average of the averages (i.e. I want to have the average of (23 + 70 + 97)/3 not the SUM(total logins) / SUM(Total Count) ), I tried AVERAGEX(dummycolumn, Student Average) but it did not show the proper value I was looking for.

Kind Regards

K

[v-jayw-msft · Accepted solution]

Hi @kabdelkarim ,

Are you want the average of all category instead of the average for each category?

If so please try the formula below.

Measure 2 = SUMX(ALL('Table'),'Table'[loging counts])/SUMX(ALL('Table'),'Table'[Total count])

Measure 1 is the formula you are using.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@kabdelkarim ,

Try like

Students Average  =avergageX(summarize(Table,Table[category],"Stu_Avg",divide(SUM(Table[Student total logins]),SUM(Table[Student Total Count])),[Stu_Avg]))
Teachers Average  =avergageX(summarize(Table,Table[category],"Tch_Avg",divide(SUM(Table[Teachers total logins]),SUM(Table[Teachers Total Count])),[Tch_Avg]))

[astojanac]

Hello @kabdelkarim ,

Can you try this please, create another measure

students avegare total = averagex(values('Category Name'), 'Students Average')

Same for teachers average

teachers avegare total = averagex(values('Category Name'), 'Teachers Average')

Best Regards, Alex