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

       

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

        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