DAX Results Above or Below Average

[cottrera]

Hi 

I require a DAX measure to work out if the number of repairs per Address line are above or below the total average for all addressess and repairs.

I have two tables 

1- Address table

2- Repairs by address row by row

Example of tables

Address Table
Address
1 Nonsense Street, London
2 Unknown Road, London
3 High Road, London
4 Dax Lane, London
5 Power Road, London
6 Excel Street, London
7 Variable Lane, London
8 Calculate Street, London
Repairs Table
Address	Repairs
1 Nonsense Street, London	Repair 1
1 Nonsense Street, London	Repair 2
2 Unknown Road, London	Repair 1
2 Unknown Road, London	Repair 2
2 Unknown Road, London	Repair 3
2 Unknown Road, London	Repair 4
2 Unknown Road, London	Repair 5
3 High Road, London	Repair 1
3 High Road, London	Repair 2
3 High Road, London	Repair 3
4 Dax Lane, London	Repair 1
5 Power Road, London	Repair 1
5 Power Road, London	Repair 2
5 Power Road, London	Repair 3
6 Excel Street, London	Repair 1
6 Excel Street, London	Repair 2
6 Excel Street, London	Repair 3
6 Excel Street, London	Repair 4
6 Excel Street, London	Repair 5
7 Variable Lane, London	Repair 1
8 Calculate Street, London	Repair 1
8 Calculate Street, London	Repair 2
8 Calculate Street, London	Repair 3
8 Calculate Street, London	Repair 4
8 Calculate Street, London	Repair 5

The visual table for the results will contain the following columns. I am using a DAX measure to count the number of repairs per address.  I have another DAX measure that works out the average number of repairs for all addressess  Count Repairs / Count Addressess = Average Repairs

Address	Count of Repairs
1 Nonsense Street, London	2
2 Unknown Road, London	5
3 High Road, London	3
4 Dax Lane, London	1
5 Power Road, London	3
6 Excel Street, London	5
7 Variable Lane, London	1
8 Calculate Street, London	5

I would like to add a 3rd columns that does the following. If Count Repairs > Average Repairs then " Above Avg" If Count Repairs < Average Repairs then " Below Avg"

thank you in advance

Richard

[cottrera]

Thank you for your quick response. Silly question but how did you calculate the count of address shown in the 3rd column

[Singaravelu_R]

Magae relation ship with Address column with Address and Repair table 

and create new table in modeling  by using summarize function

Summarize = SUMMARIZE('Address','Address'[Address ],"Repair count",count('Repair Table'[Repairs]))

[Singaravelu_R]

[cottrera]

Thank you for your quick response. Silly question but how did you calculate the count of address shown in the 3rd column

[aj1973 · Accepted solution]

Hi @cottrera 

Here is the formula

Number of repairs by address =

var _address = SELECTEDVALUE('Address'[Address])

return

CALCULATE(COUNTROWS(Repairs), Repairs[Address] = _address)

Count of address = 8

here is the formula

Count of address = CALCULATE(DISTINCTCOUNT('Address'[Address]), ALL('Address'))

Avarage repairs, here is the picture

By the way it is better to use DIVIDE for avarage repair

Avarage repairs = DIVIDE([Number of repairs by address], [Count of address], 0)