cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cottrera
Helper V
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
  
AddressRepairs
1 Nonsense Street, LondonRepair 1
1 Nonsense Street, LondonRepair 2
2 Unknown Road, LondonRepair 1
2 Unknown Road, LondonRepair 2
2 Unknown Road, LondonRepair 3
2 Unknown Road, LondonRepair 4
2 Unknown Road, LondonRepair 5
3 High Road, LondonRepair 1
3 High Road, LondonRepair 2
3 High Road, LondonRepair 3
4 Dax Lane, LondonRepair 1
5 Power Road, LondonRepair 1
5 Power Road, LondonRepair 2
5 Power Road, LondonRepair 3
6 Excel Street, LondonRepair 1
6 Excel Street, LondonRepair 2
6 Excel Street, LondonRepair 3
6 Excel Street, LondonRepair 4
6 Excel Street, LondonRepair 5
7 Variable Lane, LondonRepair 1
8 Calculate Street, LondonRepair 1
8 Calculate Street, LondonRepair 2
8 Calculate Street, LondonRepair 3
8 Calculate Street, LondonRepair 4
8 Calculate Street, LondonRepair 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

 

AddressCount of Repairs
1 Nonsense Street, London2
2 Unknown Road, London5
3 High Road, London3
4 Dax Lane, London1
5 Power Road, London3
6 Excel Street, London5
7 Variable Lane, London1
8 Calculate Street, London5

 

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
aj1973
Community Champion
Community Champion

Hi @cottrera 

Here is the formula

aj1973_0-1618845017653.png

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
aj1973_1-1618845525193.png

 

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!

View solution in original post

5 REPLIES 5
cottrera
Helper V
Helper V

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

Singaravelu_R
Resolver II
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]))

 

Singaravelu_R_0-1618835271989.png

 

 

Singaravelu_R_0-1618835704675.png

 

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

aj1973
Community Champion
Community Champion

Hi @cottrera 

Here is the formula

aj1973_0-1618845017653.png

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
aj1973_1-1618845525193.png

 

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!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors