Filtering results to only show top x results in Power View chart

By | January 25, 2013

 

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 !.

image

Then I create a chart:

image

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:

image

Now I put that measure in the map filter area and start filtering on it. This gives us only the top 2 ranked cities:

image

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.

 

  • Gavo00

    Thank you very much for this sample code Kasper. I have a quick question on best practice for multiple columns and RANKX usage and performance.

    If I have a dataset with columns A,B,C,D with values like State, Team, Metric, Qty

    I can build a 3 separate DAX RANKX expressions for Qty for columns A, B, C and this works really well for layered rankings within a nested Pivot Table.

    Is there a way to build a single RANKX expression on Qty that works for whatever column is used in a Pivot Table?

  • Kasper de Jonge

    Hi @Gavo00 ,

    Sure you can use something like ISFILTERED or HASONEVALUE to determine which field is on the field list and dynamicly determine the right rank using an IF statement.

    Hope that helps.

  • Jamie Thomson

    Hi Kasper,
    I cannot for the life of me figure this out. In your sample where does [Sum of Data] come from? I can see that intellisense makes it available to me when I’m writing the formula but I do not know why. I’m trying to replicate the same thing in something I’m working on but there is no option to choose [Sum of ]. I’m stuck!!!

    Would you mind downloading this: http://sdrv.ms/YGQAoU and having a go for yourself? I want to rank all the suppliers according to [Sum of Amount] but if you take a look in the data model you’ll see there is an error on the formula. Hoping you can figure it out.

    thanks
    Jamie

  • Kasper de Jonge

    @Jamie Thomson Sum of data was created by me adding the Data column to the pivottable. This in an implicit measure. You can create one yourself, its just a simple SUM(table[Data]).

  • Jamie Thomson

    @Kasper de Jonge
    Thanks Kasper, I’ve got it working now.

    What confused me beforehand was that in your sample I removed the pivot table but the rank measure still worked. I still don’t understand why but I’m not going to worry about it now that its working.

    Thanks again!

  • Dear Kasper,

    I still have questions about why we need a pivottable and how did you referenced to Sum of Data ???

    I have a table in which there is column “Exporter” and another column “Sales”. I look forward to summarize them based on top 5 in powerview format but i fail to do it eventhough i try your sample work.

    Can you please write it step by step so that i may follow your guidance and achieve my goal ?

    Thanks in advance for your support.

    • Kasper de Jonge

      Hi Ali,
      I am referencing an already created measure. You can replace it by SUM(Table[column])

      Hope that helps,
      Kasper

  • Hi Kesper,
    Your logic above makes perfect sense, but I can’t crack it for some reason. This is making me Sad!!!! There is obviously something missing in my logic. I have created a measure as follows

    =RANKX(ALL(Table2[City]), SUM(Table2[Sales]))

    The measure is called Ranking. However when I put this on my powerview chart the only value is 1. I want to sum the data by city and rank by the cities.
    This is my data

    Country City Sales
    Germany Munich 1
    Germany T 3
    Germany D 2
    Germany Munich 7
    Germany A 8
    Germany Munich 6
    Germany P 9

    thanks in advance

    • Kasper de Jonge

      Hi Mark,

      Do this RANKX(ALL(Table2[City]), CALCULATE(SUM(Table2[Sales])))
      or RANKX(ALL(Table2[City]), [SUM of Sales]))

  • Elliot Dixon

    Great little post. Thanks for the tip.

  • Gabriel Mercado

    If I add a legend to the powerview, the top x, the rankx stops and shows all the result..how do I fix this?