Creating industry average

[Anonymous]

Hi!

I would like to calculate an industry average of total visits. How do i do that? 

I have to tables. 

Table 1: 

Customer name	Industry name
Customer 1	Industry 1
Customer 2	Industry 2
Customer 3	Industry 1

Table 2

Customer name	Date	Visits
Customer 1	15.06.2020	102
Customer 2	15.06.2020	2034
Customer 3	15.06.2020	540
Customer 1	16.06.2020	203
Customer 2	16.06.2020	3042
Customer 3	16.06.2020	654

So the result would be: 

Total visits of customer 1 = 305

Total visits of customer 2 = 5.076

Total visits of customer 3 = 1.194
Total visits of industry 1 = 1.499 

Total visits of industry 2 = 5.076

Industry 1 average = 749,5

Industry 2 average = 5.076 

The output i want to generate is: 

Industry name	Industry average
Industry 1	749,5
Industry 2	5.076

I hope you can help me! 

[FarhanAhmed · Accepted solution]

Try by creating 2 measures. Make sure that Industry and Visits table are joined properly

m_SUMVisits = SUM(Visits[Visits])

m_Avg = AVERAGEX(VALUES(Industry[Customer name]),[m_SUMVisits])

[Anonymous]

Hi @FarhanAhmed 

Do you also happen to know how i can present the industry average next to each customers value? 

Like this: 

Customer name	Total visits	Industry average (m_avg)
Customer 1	305	1.499
Customer 2	5.076	5.076
Customer 3	1.194	1.499

When i select the measure you helped me create "m_avg" it just shows the total visits in "Industry average" 

And if you also have the time I created industry averages for other values such as "Avg. order value industry average": "

Avg. order value industry average = AVERAGEX(VALUES(Customers[Customer name]),[Avg. order value])

But i would like to weigh the values by the percentage of visits that each customer have in the industry. So i get the following results below. 

Customer name	Total visits	Industry total visits	% of industry total visits	Avg. order value
Customer 1	305	1.499	20,35	20
Customer 2	5.076	5.076	100	50
Customer 3	1.194	1.499	79,65	30

Industry	Avg. order value industry           (Old calculation result)	Avg. order value industry    (Wanted result)
Industry 1	(20+30) / 2 = 25	20*20,35%+30*79,65% = 27,965
Industry 2	50	50

[Anonymous]

Hi @FarhanAhmed 

THANK YOU SO MUCH! I love this community! 😄 

It worked like a charm! 

For anyone in the future, my final two measures was named: 

Total visits = SUM('Sessions (SessionStats)'[Total sessions])

Visits average = AVERAGEX(VALUES(Customers[Customer name]),[Total visits])