Forum Discussion
Calculate average considering all columns except one
Hi Team, I have a Table matrix with 5 columns added in it and "State" is one of the column. I want to get the average sales at the national level, i.e, that measure should ignore filter fro...
Hi Anonymous ,
Try to use allexcept and add 'country' and 'Speciality' to the filter condition.
Measure = CALCULATE(AVERAGE('Table'[sale]),ALLEXCEPT('Table','Table'[Speciality],'Table'[country]))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Greg_Deckler Hi Greg,
Below is how my table looks like. In the Average - National level, I except only 2 values, one for Rap and one for Classic. But currently its not giving me the expected result.
how can i modify my DAX to get the correct value?
| Division | Assignment Type | Speciality | State | Average -State level | Average- National level |
| North | Trans | Rap | California | $83.09 | $83.09 |
| North | Trans | Rap | Florida | $68.60 | $68.60 |
| North | Trans | Rap | Illinois | $82.80 | $82.80 |
| North | Trans | Rap | Missouri | $65.00 | $65.00 |
| North | Trans | Classic | California | $87.20 | $87.20 |
| North | Trans | Classic | Florida | $75.46 | $75.46 |
| North | Trans | Classic | Missouri | $88.00 | $88.00 |
Greg_Deckler
Community Champion
Community ChampionAnonymous What is your expected result?
Greg_Deckler For Rap, i should be getting 77.28 in all 4 & for Classic it should be 80.06.
Basically, i need a national level average.