## Desktop

Frequent Visitor
Posts: 11
Registered: ‎01-23-2017

# rankx as measure; sum of certain ranks

HI,

I tried sooo much.. hope someone can help me. Pleeease!!!

Here's my problem:

I have a table with many items and attributes.. lets say it looks like this:

Table: TableXYZ

 Country Year AttributeA AttributeB AttributeC Value China 2007 ABC b x 58 China 2007 ABC b x 720 China 2007 ABC a x 531570 China 2007 EFG a x 37602 China 2007 EFG a x 99741 China 2007 EFG b x 5465 China 2008 ABC b x 453272 China 2008 ABC b x 10528 China 2008 ABC a x 34534 China 2008 EFG a y 2941790 China 2008 EFG a x 2793761 China 2008 EFG b x 3183596 China 2009 ABC b x 2314133 China 2009 ABC b x 445879 China 2009 ABC a x 145952 China 2009 EFG a x 1339435 China 2009 EFG a x 141364 China 2009 EFG b x 1002798 USA 2007 ABC b x 317921 USA 2007 ABC b x 125046 USA 2007 ABC a x 32689 USA 2007 EFG a x 128757 USA 2007 EFG a x 139812 USA 2007 EFG b x 51770 USA 2008 ABC b x 90683 USA 2008 ABC b x 1583620 USA 2008 ABC a x 1441 USA 2008 EFG a x 4000 USA 2008 EFG a x 974673 USA 2008 EFG b x 20973 USA 2009 ABC b x 440633 USA 2009 ABC b x 1073140 USA 2009 ABC a x 2000 USA 2009 EFG a x 66162 USA 2009 EFG a x 12929 USA 2009 EFG b x 352986 Russia 2007 ABC b y 1603737 Russia 2007 ABC b x 339277 Russia 2007 ABC a x 1389284 Russia 2007 EFG a x 610817 Russia 2007 EFG a x 1655642 Russia 2007 EFG b x 704668 (simplified, more countries and attributes in orig. file)

What i want and what works:

1. Rank Country within Year according sum of Value with a slicer filtering Atributes A and B. Attribute C is used to exclude certain values:

Here is how I have done it:

Rank Country = IF(HASONEVALUE(TableXYZ[Country]); RANKX ( ALL(TableXYZ[Country]); [Selected Values]; ; DESC; DENSE );BLANK())

with

[Selected Values]=CALCULATE(SUM(TableXYZ[Value]);TableXYZ[AttributeC]<>"y";ALLEXCEPT(TableXYZ;TableXYZ[Country];TableXYZ[AttributeA];TableXYZ[AttributeB];TableXYZ[Year]))

The ranks of the countries within the year for the selected Attribute(AandB) combination works within a matrix (Country - Value)  and for stacked column charts (Country is column series).

2. Show only certain ranks works with a measure within the visual level filters:

Show certain ranks = IF([Rank Country]>[Selected TopX];BLANK();[Rank Country]) (the filter is set to "not Blank")

With [Selected TopX] beeing a measure returning the selected value of a column "TopX" of a second table called "Ranking" via a slicer.

Selected TopX = MAX(Ranking[TopX])

What is not working and returning a false result is (HERE IS THE QUESTION):

I can not sum up the selected ranks!! It gives my funny results, sometimes quite close or correct ones. For example if I want to show in a Card the sum of the selected Ranks (e.g. top 2) of each year.  Tried with the following, but it returns not the right sum:

Sum of Selected TopX = CALCULATE( SUM( TableXYZ[Value] ); FILTER( TableXYZ; [Rank Country]<=[Selected TopX] ) )

I tried also several other things like TOPN, experiemented with GROUPBY, read about inner and outer Filter relationships ans so on.. but finaly im quite helpless..

I think it is challenging, as Rank Country must be a measure and not a column because of the slicer selections/combinations/dynamic behaviour.

I tried to recreate this phenomenon with a more simplistic table, but it works for a table like the shown above.. could it be related to processing capacity?

I hope I explained it sufficiently and there is someone who likes to help me!!

Best regards!!

Accepted Solutions
Frequent Visitor
Posts: 11
Registered: ‎01-23-2017

## Re: rankx as measure; sum of certain ranks

[ Edited ]

Hi @Sean

I don't now why it wasn't working before, but after building every from scratch, it now works as I like it to work.

Even it slighly differs from your version, I like to thank you very very much!!! And great GIFs, that is realy nice as a visual support!

An example of what I wanted to achive is in the attatched file (dropboxlink), the measures are the following:

Rank Country = IF(HASONEVALUE(TableXYZ[Country]); RANKX ( ALL(TableXYZ[Country]); [Selected Values]; ; DESC; DENSE );BLANK())

Selected Values = CALCULATE(SUM(TableXYZ[Value]);TableXYZ[AttributeC]<>"y";ALLEXCEPT(TableXYZ;TableXYZ[Country];TableXYZ[AttributeA];TableXYZ[AttributeB];TableXYZ[Year]))

Show certain ranks  = IF(AND([Selected TopX]=99;[Rank Country]<=20);BLANK();IF([Rank Country]>[Selected TopX];BLANK();[Rank Country]))

Selected TopX = MAX(Ranking[TopX])

Sum of Selec of Selected TopX = IF([Selected TopX]=99;CALCULATE([Selected Values];FILTER(TableXYZ;[Rank Country]<=[Selected TopX]))-CALCULATE([Selected Values];FILTER(TableXYZ;[Rank Country]<=20));CALCULATE([Selected Values];FILTER(TableXYZ;[Rank Country]<=[Selected TopX])))

https://www.dropbox.com/s/dzsz3jprapv24m0/RankHelp.pbix?dl=0

I would be interested in your comment on this As far as I tried it with my original databasis it works, but maybe that was only luck.

Best regards and thanks again,

Cheers!

All Replies
Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: rankx as measure; sum of certain ranks

[ Edited ]

@craasp

This Measure should work! You were very close...

You were summing all values instead of using the Measure that filters the "y"s

```Sum of Selected TopX =
CALCULATE (
[Selected Values],
FILTER ( ALL ( TableXYZ[Country] ), [Rank Country] <= [Selected TopX] )
)```

You mentioned you tried TOPN as well

This should give you the same result using TOPN

```Sum of Selected TopX2 =
CALCULATE (
[Selected Values],
TOPN ( [Selected TopX], ALL ( TableXYZ[Country] ), [Rank Country], ASC )
)```

Hope this helps!

Frequent Visitor
Posts: 11
Registered: ‎01-23-2017

## Re: rankx as measure; sum of certain ranks

[ Edited ]

Unfortunately that was one of many versions I tried also.. but it does not work. If I only knew why, because I think it should be done like you described...

One thing though, it should be displayed per year.

The version you proposed gives me for every year the same number.

I checked again, the 1000st time, but it wont work properly.

If I'd use only "TableXYZ" instead of "All(TableXYZ[Country])" within the "Sum of Selected TopX" measure it would give me for each year a different result, quite close to the right result but still wrong.

second approach: If I would use "TableXYZ" in "Sum of Selected TopX" and another Rank measure (namely this one: Rank Country 2 = IF(HASONEVALUE(TableXYZ[Country]); RANKX ( ALLSELECTED(TableXYZ); [Selected Values]; ; DESC; DENSE );BLANK()) )

it gives me the right result. ergo:

```Sum of Selected TopX =
CALCULATE (
[Selected Values],
FILTER (TableXYZ, [Rank Country 2] <= [Selected TopX] )
)```

BUT i want to apply another slicer to the measure "Sum of Selected Topx 2" which does not affect the ranking but excudes Countries with certain attributes. If I try this, the measure brakes..

I tried to use a modified "Selected Values" in "Sum of Selected TopX"  (the modified one is only there used, not in the ranks measure) where the column with the mentioned attribute is included in the AllEXCEPT part. .... it works, but only for some years..???

This second approach also does also not work in a matrix nor as linechart.. (in my opinon it shouldn't work in the first place as the rank should not result in the right ranks)

One big questionmark still for me, although i think there is only a little detail I'm missing.

cheers!

EDIT1: second approach is BS!!

Edit2: second approach would work in a card, if I ditch the external slicer. But still BS as I need it for a line chart or column chart without column series.

Highlighted
Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: rankx as measure; sum of certain ranks

@craasp

Okay I think this should do it!

```Sum of Selected TopX =
CALCULATE (
[Selected Values],
FILTER ( ALL ( TableXYZ[Country], TableXYZ[Year] ), [Rank Country] <= [Selected TopX] )
)```

Frequent Visitor
Posts: 11
Registered: ‎01-23-2017

## Re: rankx as measure; sum of certain ranks

[ Edited ]

Hi @Sean

I don't now why it wasn't working before, but after building every from scratch, it now works as I like it to work.

Even it slighly differs from your version, I like to thank you very very much!!! And great GIFs, that is realy nice as a visual support!

An example of what I wanted to achive is in the attatched file (dropboxlink), the measures are the following:

Rank Country = IF(HASONEVALUE(TableXYZ[Country]); RANKX ( ALL(TableXYZ[Country]); [Selected Values]; ; DESC; DENSE );BLANK())

Selected Values = CALCULATE(SUM(TableXYZ[Value]);TableXYZ[AttributeC]<>"y";ALLEXCEPT(TableXYZ;TableXYZ[Country];TableXYZ[AttributeA];TableXYZ[AttributeB];TableXYZ[Year]))

Show certain ranks  = IF(AND([Selected TopX]=99;[Rank Country]<=20);BLANK();IF([Rank Country]>[Selected TopX];BLANK();[Rank Country]))

Selected TopX = MAX(Ranking[TopX])

Sum of Selec of Selected TopX = IF([Selected TopX]=99;CALCULATE([Selected Values];FILTER(TableXYZ;[Rank Country]<=[Selected TopX]))-CALCULATE([Selected Values];FILTER(TableXYZ;[Rank Country]<=20));CALCULATE([Selected Values];FILTER(TableXYZ;[Rank Country]<=[Selected TopX])))

https://www.dropbox.com/s/dzsz3jprapv24m0/RankHelp.pbix?dl=0

I would be interested in your comment on this As far as I tried it with my original databasis it works, but maybe that was only luck.

Best regards and thanks again,

Cheers!