Average of average of average in power BI

[menik_sun]

Hi,

Below is how my data looks like:

Selling Channel	Month	Transaction No	Sales Amount
Store	Jan	1	200.00
Store	Jan	2	400.00
Store	Jan	3	600.00
Store	Jan	4	800.00
Online	Jan	5	200.00
Online	Jan	6	400.00
Online	Jan	7	600.00
Store	Feb	8	800.00
Store	Feb	9	200.00
Store	Feb	10	300.00
Online	Feb	11	400.00
Online	Feb	12	600.00
Online	Feb	13	800.00
Online	Feb	14	200.00
Store	Mar	15	400.00
Store	Mar	16	600.00
Store	Mar	17	800.00
Store	Mar	18	200.00
Online	Mar	19	400.00
Online	Mar	20	600.00
Online	Mar	21	800.00

When I use average function in Power BI, I get average based on no of transactions happened during the month or channel. But, I want to get average of average of data like below.

		Avg Selling- How I want to see	Avg Selling- How I get using average formula
Jan	Average	450.00	457.14
	Store	500.00	500.00
	Online	400.00	400.00
Feb	Average	466.67	471.43
	Store	433.33	433.33
	Online	500.00	500.00
Mar	Average	550.00	542.86
	Store	500.00	500.00
	Online	600.00	600.00

I want to get the average of average value of Store & Online for any month (or vice-versa), assuming Store & Online counting it as 1 each (not as per total count of transactions). This is because to check the actual average selling and avoiding any big selling in any of the channel or any month.

Can you please help

[v-shex-msft]

Hi @menik_sun,

It sounds like a common multiple aggregate requirement, you can take a look at the following blog to know how to achieve this.

Measure Totals, The Final Word 

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@menik_sun , Try like

avergageX(summarize(Table, Table[Selling Channel], Table[Month] ,"_1",average(Table[Sales Amount])),[_1])