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!
Sample File-Prevailing Average, % of prevailing average.pbix (49.1 MB)
