Forum Discussion
Calculate average based on filter selected
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
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.
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
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