Forum Discussion

totamum's avatar
totamum
Helper I
1 year ago
Solved

Indicate whether data is above or below average

I'm trying to create a card that will say whether a data item is above or below average.  I came across an IF formula on another post and it looks like it should work great but when entering the formula, I'm finding only the measures in the table are available to select and not my other data items.  I wanted to show if Page Views is above or below average using the Average Page Views measure I have.  Here is my table and formula below.  Any ideas?  

 

 

 

Column = 
IF (
    'Table'[abc] > [Average of abc],
    "Above Average",
    IF (
        'Table'[abc] < [Average of abc],
        "Below Average",
        "Equal Average"
    )
)

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi totamum ,

     

    First of all thanks to lbendlin  for the quick reply. I have some other thoughts to add:

    (1) This is my test data.

    (2) We can create a average measure.

    Average Page Views = AVERAGEX(ALLSELECTED('Table'),[Page Views])

    (3) We can create a conditional column or a conditional measure.

    Column = 
IF (
    [Page Views] > [Average Page Views],
    "Above Average",
    IF ( [Page Views] < [Average Page Views], "Below Average", "Equal Average" )
)

     

    Measure = IF (
    SUM('Table'[Page Views]) > [Average Page Views],
    "Above Average",
    IF (
        SUM('Table'[Page Views]) < [Average Page Views],
        "Below Average",
        "Equal Average"
    )
)

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi totamum ,

     

    First of all thanks to lbendlin  for the quick reply. I have some other thoughts to add:

    (1) This is my test data.

    (2) We can create a average measure.

    Average Page Views = AVERAGEX(ALLSELECTED('Table'),[Page Views])

    (3) We can create a conditional column or a conditional measure.

    Column = 
IF (
    [Page Views] > [Average Page Views],
    "Above Average",
    IF ( [Page Views] < [Average Page Views], "Below Average", "Equal Average" )
)

     

    Measure = IF (
    SUM('Table'[Page Views]) > [Average Page Views],
    "Above Average",
    IF (
        SUM('Table'[Page Views]) < [Average Page Views],
        "Below Average",
        "Equal Average"
    )
)

    Best Regards,

    Neeko Tang

    If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    You created a column.  You cannot meaningfully use measures in calculated columns. Did you mean to create a measure?