# Prevailing Average, Difference & % Difference filtered by a parameter query

Hi. I am stuck on finding the correct formula to find the prevailing average of a measure that is filtered by a parameter query. I have been working on this for several days now, googling and watching you tube videos. The kicker is the parameter query that is controlling which cities are displayed.

I have a list of cities and the amount of taxes that they have received each year. I am trying to find the average taxes of the cities that are displayed. I have a formula that is getting close, but is not quite the correct answer. This is

from a demo fileâ€“so it doesnâ€™t have any formatting, etcâ€“just enough to get the information across for this question.

I have a parameter query where the user can choose the minimum sales tax and maximum sales tax that they want to compare.

The page looks as follows.

The measure that is calculating Prevailing Avg Taxes-Bands-Not Correctâ€“is wrongâ€“the prevailing average for this set of data should be \$10,701. The measure is as follows.

`Prevailing Avg Taxes-bands-not correct = (CALCULATE( AVERAGEX(CitySalesTaxCollections,CitySalesTaxCollections[Total]),filter(CitySalesTaxCollections,[Total Taxes Filter]=1)))`

The answer is closeâ€“but not quite there.

I have a measure for Total Taxesâ€“which is

``````Total Taxes = (SUM('CitySalesTaxCollections'[Total]))
``````

Howeverâ€“I wasnâ€™t able to make a formula work using this measureâ€“so I went back to the table level of data.

I am also wanting to calculate the Difference between each cities taxes in the table and the prevailing average, then calculate the % of how far above or below they are from the average. Below is an excel table showing basically what I am looking for. I donâ€™t have to have the Prevailing average inside the tableâ€“I just need to be able to find that number for the rest of the formulas to calculate correctly.

Any assistance would be greatly appreciated!

Create a measures like below

1 Like

Woo, Hoo!! Thank you so much, @Rajesh!!! It works perfectly!! I have been working on this for days. I really appreciate your help!!