Forum Discussion

Arete's avatar
Arete
Icon for Helper I rankHelper I
3 years ago
Solved

X-Axis Constant Line with RankX

Hello Community!

 

I'd like to put an X-Axis Constant line on a Scatter Chart for the 10th largest Entity and I've created 3 measures:

 

Total Entities = 
CALCULATE(
    COUNT('tbl'[NER_PII_Values])
)
Total Entities Ranking = 
RANKX( 
    ALL('tbl'[NER_PII_Values]),
    [Total Entities],
    ,DESC
)
Entity Distribution X Constant = 
VAR TotalEntityRanking = [Total Entities Ranking]
RETURN
CALCULATE( 
    [Total Entities],
    FILTER(
        'tbl',
        TotalEntityRanking = 10
    )
)

 

On the Matrix, it looks great but when I use the measure in conditional formatting it shows as 0:

 

 

I'm expecting a line at 71,656.

 

Thank you in advance!

 

Need Help
  • FreemanZ's avatar
    FreemanZ
    3 years ago

    hi Arete 

    tried to simulate your issue and find the solution with such sample data:

    create a measure:

     

     

    Value10th = 
VAR _table = 
ADDCOLUMNS(
    TableName,
    "Rank",
    RANKX(
        TableName,
        TableName[Value1],
        ,
        ASC
    )
)
RETURN
MINX(
    FILTER(_table, [Rank] = 10),
    TableName[Value1]
)

     

     

     

    it worked like this:

     

3 Replies

  • FreemanZ's avatar
    FreemanZ
    Icon for Super User rankSuper User
    3 years ago

    hi Arete 

    tried to simulate your issue and find the solution with such sample data:

    create a measure:

     

     

    Value10th = 
VAR _table = 
ADDCOLUMNS(
    TableName,
    "Rank",
    RANKX(
        TableName,
        TableName[Value1],
        ,
        ASC
    )
)
RETURN
MINX(
    FILTER(_table, [Rank] = 10),
    TableName[Value1]
)

     

     

     

    it worked like this:

     

    • Arete's avatar
      Arete
      Icon for Helper I rankHelper I
      3 years ago

      Hi, FreemanZ  thanks for the reply. Unfortunately, it's still not working. Is it because I'm using a measure in the RANKX?

      Entity Distribution X-Axis line = 
VAR tblRank = 
ADDCOLUMNS(
    'Top 250 Entities',
    "Rank",
    RANKX(
        'Top 250 Entities',
        [Total Entities],
        ,
        DESC
    )
)
RETURN
MINX(
    FILTER(tblRank, [Rank] = 10),
    [Total Entities]
)


      The Total Entities measure is just a count:

       

      Total Entities = 
CALCULATE(
    COUNT('Top 250 Entities'[NER_PII_Values])
)


      Your suggestion comes back with Blank when it should be 71.624:

       



      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User
        3 years ago

        It depends. It might be on different granularities. Try to replace the measure with a column reference: 'Top 250 Entities'[NER_PII_Values]