A question came up recently on how can I limit the number of elements on a chart in Power View by a custom calculation. Imagine you have a chart in Power View with too many data points to make anything out and you want to filter out the “noise”. Just give me the most profitable datapoints.
I created this chart with just a few datapoints. I love how Power View and Bing maps allow me to put any location in and it just knows where to plot it !.
Then I create a chart:
Now I create a DAX measure that determines the Rank of each postalcode by sum of data:
=RANKX(ALL(CityData[PostalCode]),[Sum of Data])
This measure will compare the result of [Sum of Data] for the current data point against the result of [Sum of Data] for all rows in ALL(CityData[PostalCode]) and return the relative rank of those two numbers.
In a table this shows us as following:
Now I put that measure in the map filter area and start filtering on it. This gives us only the top 2 ranked cities:
The rank is automatically measure calculated for each data point in the chart (like what you see in the table in the previous image).
Download the sample workbook here.