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.
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?
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.
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
@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]).
@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.
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
Hi Mark,
Do this RANKX(ALL(Table2[City]), CALCULATE(SUM(Table2[Sales])))
or RANKX(ALL(Table2[City]), [SUM of Sales]))
Great little post. Thanks for the tip.
If I add a legend to the powerview, the top x, the rankx stops and shows all the result..how do I fix this?