Forum Discussion

ktt777's avatar
ktt777
Helper V
6 years ago

Compare with average value

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 . 
Need Help

4 Replies

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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)