Calculate average based on filter selected

[Anonymous]

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

[Habib]

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]

@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 · Accepted solution]

@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