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

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 Smiley Happy As far as I tried it with my original databasis it works, but maybe that was only luck.

 

Best regards and thanks again,

Cheers!

View solution in original post


All Replies
Super Contributor
Posts: 1,947
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! Smiley Happy

 

Rankx - Top 3.gif

 

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

Re: rankx as measure; sum of certain ranks

[ Edited ]

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

Highlighted
Super Contributor
Posts: 1,947
Registered: ‎08-11-2015

Re: rankx as measure; sum of certain ranks

@craasp

Okay I think this should do it! Smiley Happy

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

Rankx - Top 3 (2).gif

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 Smiley Happy As far as I tried it with my original databasis it works, but maybe that was only luck.

 

Best regards and thanks again,

Cheers!