Forum Discussion

CAPEconsulting's avatar
CAPEconsulting
Icon for Helper III rankHelper III
6 years ago

Rankx and summarized table

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

5 Replies

  • stevedep's avatar
    stevedep
    Icon for Memorable Member rankMemorable Member
    6 years ago

    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's avatar
      CAPEconsulting
      Icon for Helper III rankHelper III
      6 years ago

      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's avatar
        CAPEconsulting
        Icon for Helper III rankHelper III
        6 years ago

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