rankx as measure; sum of certain ranks

[craasp]

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

[Sean]

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

[craasp]

@Sean thank you very much vor your fast answer!!

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.

Thanks again for your help,

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.

[Sean]

@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] )
)

[craasp · Accepted solution]

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!