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