cancel
Showing results for
Search instead for
Did you mean:
Helper V

## DAX Results Above or Below Average

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

1 ACCEPTED SOLUTION
Community Champion

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)

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!
5 REPLIES 5
Helper V

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

Resolver II

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]))

Resolver II

Helper V

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

Community Champion

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)

Regards
Amine Jerbi
https://www.linkedin.com/in/amine-jerbi-2b3a8928/

Did I answer your question? Mark my post as a solution!

## Helpful resources

Announcements

#### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

#### Claim Your Badge & Digital Swag!

Check out how to claim yours today!

#### Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors