Rankx and summarized table

[CAPEconsulting]

I have a complex table where a column is flagged as denominator based on some variables and another column is flagged as numerator based on other criteria. Then a measure of proportion is calculated as the sum of all items flagged ad numerator divided by all items flagged as denominator. This is working fine.

Now I need to rank the clients based on the proportion while filtering out the clients that have less than 10 as the denominator.

Just to work out my logic I did a calculated tables as follows

Table = FILTER(

SUMMARIZECOLUMNS(sun[Domain], sun[Date], sun[Attribute], sun[Account Name], sun[Territory], "Num", [Current Denominator], "Denom", [Current Denominator], "Score", [Current %]) , [Denom] > 10)

Then this table was used to create 

ranker =

VAR Ranky = RANKX( ALL('Table'[Account Name]), SUM( 'Table'[Score]), , ASC)

RETURN

IF( Ranky <= 5, SUM( 'Table'[Score]), BLANK())

This hence gives me the bar chart showing just the bottom 5 values.

Now I want to do the above as a measure rather than a physical table.

But when I try this  

RANKX ( FILTER
FILTER(

SUMMARIZECOLUMNS(sun[Domain], sun[Date], sun[Attribute], sun[Account Name], sun[Territory], "Num", [Current Denominator], "Denom", [Current Denominator], "Score", [Current %]) , [Denom] > 10), ALL(sun[Account Name])),  [Current %], , ASC, DENSE))

It keeps giving me all sort off errors like multiple filters were passed through rankx etc. I have amended many things but can't seem to replicate the physical table work in a virtual table measure based calculation.

Any help....

[stevedep]

Hi,

This is what I have:

rank = 
var __filtertbl = FILTER(ALL('Table');'Table'[Denominator]>10)
var __productsthatmeetfilter = CALCULATETABLE(ALLSELECTED('Table');__filtertbl)

return

//CALCULATE(CONCATENATEX(ALLSELECTED(Prods[Prod]);[Prod];"-"); __filtertbl) // to test / debug
MAXX('Table';IF('Table'[Denominator]>10;
RANKX(__productsthatmeetfilter;CALCULATE(MINX('Table';
'Table'[Numerator]/'Table'[Denominator]));;ASC;Skip)
; BLANK()))

As seen here:

File is here. 

Please mark as solution if this works for you. Thumbs up for the effort is appreciated.

Kind regards, Steve. 

[CAPEconsulting]

@stevedep that did not work. just to clarify numerator and denominator are measures and not columns. For example 
Current Denominator = VAR Maxdate = CALCULATE( MAX( sun[Date]), ALLSELECTED(sun[Domain]), ALLSELECTED( sun[Attribute] ), ALLSELECTED( Calendar[Date] ))
RETURN
CALCULATE( [MeasureSum] , FILTER( SUMMARIZE( sun, sun[Denom], sun[Date] ), sun[Denom] = "Denom" && sun[Date] = Maxdate ))

Since my post I have tried the following and it work for most of it

Rank =
VAR Maxdate = CALCULATE( MAX( sun[Date]), ALLSELECTED(sun[Domain]), ALLSELECTED( sun[Attribute] ), ALLSELECTED( Calendar[Date] ))
VAR RankingTable = FILTER( ALLSELECTED(sun[Account Name]), [Current Denominator] > 10)
RETURN
RANKX( RankingTable, CALCULATE([Current %], FILTER(VALUES(Calendar[Date]), Maxdate)), ,ASC,Dense)

But despite of the FILTER( ALLSELECTED(sun[Account Name]), [Current Denominator] > 10), it still ranks entities with denominator less than 10. So not sure what I am missing

[CAPEconsulting]

@Ashish_Mathur @v-juanli-msft @Phil_Seamark any suggestions.

Also is there a way to visualise the account catgeory based on the top 5 account names in a card/ chart visualisation.

Additionally I am using this for the best score to just show the best score as a figure without having the need for account names.

VAR Tabless = ADDCOLUMNS( SUMMARIZE(sun, sun[Account Name], sun[Territory]), "Scores", [Current %], "Ranks", [Rank])

RETURN

MINX( Tabless, [Scores])

[CAPEconsulting]

See using what I have the measure is still ranking an entity that has denominator less than 10.

Also please see top 5 table works fine but when territory is introduced it goes haywire

And it does not show just the top 5 anymore but starts showing all entities even though the measure uses top 5

[stevedep]

Perhaps you need to use nested interators to ensure values are calculated on the right level.