topic Re: Rankx and summarized table in DAX Commands and Tips

Rankx and summarized table

CAPEconsulting — Sun, 14 Jun 2020 08:58:43 GMT

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

Re: Rankx and summarized table

stevedep — Mon, 15 Jun 2020 07:09:56 GMT

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.

Re: Rankx and summarized table

CAPEconsulting — Tue, 16 Jun 2020 01:35:59 GMT

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

Re: Rankx and summarized table

CAPEconsulting — Tue, 16 Jun 2020 03:14:40 GMT

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

Re: Rankx and summarized table

CAPEconsulting — Tue, 16 Jun 2020 03:33:07 GMT

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

Re: Rankx and summarized table

stevedep — Tue, 16 Jun 2020 09:09:47 GMT

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