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

       

       

       

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

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