Compare with average value

[ktt777]

Hi Everyone, 

I wanted to count the number of car travelled below the average : 

Number of car = CALCULATE(COUNTROWS('Performance'),FILTER('Performance ','Performance '[Status]="Working"), FILTER('Performance ','Performance'[Km Run]<'Performance'[Average Km]))

Not sure why when i want to draw a graph, Number of car value does not show up

Thanks . 

[Greg_Deckler]

@ktt777 - Not clear what the issue is. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[ktt777]

Here is the link to my sample file:

https://1drv.ms/u/s!ApIDnMK2eKiFgQH7uX4iZE6DWw-l?e=hOO0RT

I try to count the number of employee in the West whose sale is less than average . 

Thanks 

[harshnathani]

Hi @ktt777 ,

Incase you want to calculate count of employees under average of entire table.

Count Employees under ALL average =

var numerator = CALCULATE(SUM(Sheet1[Sale ]),FILTER(ALL(Sheet1),Sheet1[Status]="Active"))

var denominator = CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1), Sheet1[Status]="Active"))

var _ALLAVERAGE = divide (numerator, denominator)

RETURN

CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1) ,Sheet1[Status]="Active" && _ALLAVERAGE > Sheet1[Sale ]))

ALL Average = var numerator = CALCULATE(SUM(Sheet1[Sale ]),FILTER(ALL(Sheet1),Sheet1[Status]="Active"))

var denominator = CALCULATE(COUNTROWS(Sheet1),FILTER(ALL(Sheet1), Sheet1[Status]="Active"))

return divide (numerator, denominator)

Incse you want to calculate count of employees under average of that particular month

Count Employees under month average =

var numerator = CALCULATE(SUM(Sheet1[Sale ]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Month]),Sheet1[Status]="Active"))

var denominator = CALCULATE(COUNTROWS(Sheet1),FILTER(ALLEXCEPT(sheet1,Sheet1[Month]), Sheet1[Status]="Active"))

var _MonthAverage = divide (numerator, denominator)

return

CALCULATE(COUNTROWS(Sheet1),FILTER(ALLEXCEPT(Sheet1,Sheet1[Month]) ,Sheet1[Status]="Active" && _MonthAverage > Sheet1[Sale ]))

Month Average = var numerator = CALCULATE(SUM(Sheet1[Sale ]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Month]),Sheet1[Status]="Active"))

var denominator = CALCULATE(COUNTROWS(Sheet1),FILTER(ALLEXCEPT(sheet1,Sheet1[Month]), Sheet1[Status]="Active"))

return divide (numerator, denominator)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[ktt777]

it works. 

thanks alot