Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculate average based on filter selected

Hi All, 

 

My original dataset has 14 rows and 2 column Company and Gender. 

 

Column_1 = CALCULATE(COUNTROWS(Sheet1),ALLEXCEPT(Sheet1,Sheet1[Company]))

Column_2 = CALCULATE(COUNTROWS(Sheet1),ALLEXCEPT(Sheet1,Sheet1[Gender],Sheet1[Company]))

Column_3 = DIVIDE(Sheet1[Column_2],Sheet1[Column_1])

 

I want to calculate Average Column, which is average of of Male and Female from column 3.

For Male it should be (.50+.29+.67)/3 =48.41 % 

 

How can i evaluate Average Column. Please guide. Thanks a Lot

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Habib : Just realised the solution with Measure

     

    Measure_1 = CALCULATE(COUNTROWS(Sheet1),ALLEXCEPT(Sheet1,Sheet1[Company]))

    Measure_2 = COUNTROWS(Sheet1)

    Measure_3 = [Measure_2]/[Measure_1]

     

    Measure_4 = CALCULATE((AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Company]), [Measure_3]) ),ALLEXCEPT(Sheet1,Sheet1[Gender]))

     

    No Need to create additional table as i did before. Thanks

3 Replies

  • Habib's avatar
    Habib
    Continued Contributor
    9 years ago

    Hi Anonymous,

     

    Average can be calculated using below formula, but in your scenario, it will not give you correct answer always.

     

    Avg = CALCULATE(AVERAGE(Sheet1[Column_3]),ALLEXCEPT(Sheet1,Sheet1[Gender]))

    As you mentioned that result should be 48.41% but if you actually calculate % of M among your dataset, it should be 43% consiering your have 6 records for M. Better would be calculate the % using below DAX without involving the Column_3.

     

    NewAvg = SWITCH(Sheet1[Gender],"F",COUNTAX(FILTER(Sheet1, Sheet1[Gender]="F"),Sheet1[Gender])/COUNTA(Sheet1[Gender]),"M",COUNTAX(FILTER(Sheet1, Sheet1[Gender]="M"),Sheet1[Gender])/COUNTA(Sheet1[Gender]))

    Hope this will help you.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Habib :  I can't consider 6 records as i have to filter the record (i.e. summarize 1 row for each [company & Gender]).

      So if you see my filter table it has only 3 rows for male and 3 rows for female based on company & Gender.

      Column_1 is number of rows for a particular company and Column_2 is number of Female or Male in that company. 

       

      In your solution , i will always have total number of rows for Female or Male from full table. 

       

      I found the solution : What i did , i created a New Table from the filtered table and then applied average and counta functiona to get the result

       

      I am still not sure , if i dont create a New Table , how can i get 3 records using DAX. Thanks

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Habib : Just realised the solution with Measure

         

        Measure_1 = CALCULATE(COUNTROWS(Sheet1),ALLEXCEPT(Sheet1,Sheet1[Company]))

        Measure_2 = COUNTROWS(Sheet1)

        Measure_3 = [Measure_2]/[Measure_1]

         

        Measure_4 = CALCULATE((AVERAGEX(SUMMARIZE(Sheet1,Sheet1[Company]), [Measure_3]) ),ALLEXCEPT(Sheet1,Sheet1[Gender]))

         

        No Need to create additional table as i did before. Thanks