Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |