Rank as measure and sum of certain ranks

[craasp]

HI, 

I tried sooo much.. hope someone can help me, resp. explain to me if my measures are not correct..

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
Russia	2008	ABC	b	x	2479340
Russia	2008	ABC	b	x	216863
Russia	2008	ABC	a	x	32020
Russia	2008	EFG	a	x	89785
Russia	2008	EFG	a	x	99424
Russia	2008	EFG	b	x	62100
Russia	2009	ABC	b	x	66055
Russia	2009	ABC	b	x	428085
Russia	2009	ABC	a	x	527690
Russia	2009	EFG	a	x	80526
Russia	2009	EFG	a	x	89918
Russia	2009	EFG	b	x	363434

(simplified, more attributes and Countries in orgi. 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!! 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 and 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 problem with a simplistic table like above to upload a example, but it works with those tables. Could it be a problem of restricted processing capacity?

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

Best regards!!

[v-shex-msft]

Hi @craasp,

>>I tried to recreate this problem with a simplistic table like above to upload a example, but it works with those tables. Could it be a problem of restricted processing capacity?

Did you want to use a measure to calculate the total value such as the matrix column total feature?

If this is a case, you can refer to below formula:

Sum of Selected TopX = 
CALCULATE( SUM( TableXYZ[Value] ), FILTER(ALLSELECTED(TableXYZ), [Rank Country]<=[Selected TopX]
	&&IF(SUM(TableXYZ[Value])<>SUMX(ALLSELECTED(TableXYZ),[Value]),
		[Year]=MAX([Year]),
		TRUE())
		))

 Add a condition filter on year column(it will be disabled on total row).

Regards,

Xiaoxin Sheng