Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

ranking visualisation

Hello,

 

I am using a Line and Stacked Column chart to show the ranking of a particular value and how it is positioned within a Red, Amber and Green grading.

 

The problem is that since I am using the percentile, and I get lots of outliers on the Red grading, the Red bar is showing too much and almost hidding the lower values.

 

Anyone aware of a visual, or a different way, to show the ranking of a value across a range of values?

I have also been looking at the RANKX function but could not get it to work.

 

My list of values is on the ProjectAuditsDB table.

the KPI Warnings_Value measure contains the value I want to rank.

 

test RANK = RANKX(ProjectAuditsDB,ALL(ProjectAuditsDB),[KPI Warnings_Value])
produces this error
"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."
 
Thank you for your help.
 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous ,

    Maybe you can remove sum function to directly compare column value and measure result:

    test RANK = RANKX(ProjectAuditsDB,ProjectAuditsDB[column1],[KPI Warnings_Value])

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

    This issue more related to your expression, you can't ranking table or multiple columns in expressions.

     

    test RANK = RANKX(ProjectAuditsDB,ALL(ProjectAuditsDB),[KPI Warnings_Value])

     

    I modified your formula as below:

     

    test RANK = RANKX(ProjectAuditsDB,sum(ProjectAuditsDB[column1]),[KPI Warnings_Value])

     

    Use of RANKX in Power BI measures

    Regards,
    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thank you Anonymous 

       

      Your expression is not producing an error, thank you.

      But the ranking value is not what I expected it to be.

       

      My measure 

      KPI Warnings_Value
      is producing a value of
      69
       
      And what I wanted was to check in which position is it in the column that includes a list of values in the ProjectAuditsDB table. Currently, your expression is producing a value of 29 whereas it should be 53.
       
      This is the full column of values I have for the Warnings/100 elements column.
       
      Thank you for your help.
       
      Warnings / 1000 Elements
      919
      542
      519
      506
      478
      432
      431
      403
      380
      380
      376
      365
      316
      311
      286
      274
      261
      256
      242
      236
      215
      196
      192
      185
      177
      166
      145
      145
      139
      129
      129
      113
      113
      113
      102
      99
      98
      94
      91
      90
      85
      85
      82
      80
      80
      79
      79
      78
      76
      75
      74
      73
      69
      69
      65
      64
      62
      62
      62
      58
      57
      57
      55
      54
      54
      53
      50
      49
      49
      49
      48
      48
      47
      47
      46
      45
      45
      45
      44
      44
      44
      43
      43
      42
      42
      40
      40
      40
      40
      40
      39
      39
      39
      39
      38
      38
      38
      37
      36
      35
      33
      33
      33
      33
      33
      33
      32
      32
      31
      31
      31
      31
      31
      31
      30
      30
      30
      30
      28
      28
      28
      28
      28
      28
      27
      27
      27
      27
      27
      27
      27
      26
      26
      26
      26
      26
      25
      25
      25
      25
      25
      25
      24
      24
      24
      24
      23
      22
      22
      22
      22
      21
      21
      21
      21
      20
      20
      20
      20
      19
      19
      19
      19
      19
      19
      18
      18
      18
      18
      18
      18
      18
      18
      18
      17
      17
      17
      17
      17
      17
      17
      17
      16
      15
      15
      15
      14
      14
      14
      13
      13
      13
      13
      13
      13
      13
      12
      12
      11
      11
      11
      11
      11
      11
      11
      11
      10
      10
      10
      10
      10
      10
      10
      10
      9
      9
      9
      9
      9
      9
      9
      9
      9
      9
      9
      9
      9
      8
      8
      8
      8
      8
      7
      7
      7
      7
      7
      7
      7
      7
      7
      7
      7
      7
      7
      7
      7
      6
      6
      6
      6
      6
      6
      6
      6
      6
      6
      5
      5
      5
      5
      5
      5
      5
      5
      4
      4
      4
      4
      4
      4
      4
      4
      4
      4
      3
      3
      3
      3
      3
      3
      3
      3
      3
      3
      3
      2
      2
      2
      2
      2
      2
      2
      2
      2
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      1
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      0
      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        HI Anonymous ,

        Maybe you can remove sum function to directly compare column value and measure result:

        test RANK = RANKX(ProjectAuditsDB,ProjectAuditsDB[column1],[KPI Warnings_Value])

        Regards,

        Xiaoxin Sheng