Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating a column that shows above or below average

I have a column with daily records of rainfall. I want to create a new column that calculates if a certain day is above/below the average rainfall over the course of the year. 

It should go something like IF(Rainfall, > Average(Rainfall), "Above Average", < Average(Rainfall), "Below Average")

Clearly this measure is wrong, just wanted to give an idea of what I'm trying to accomplish, thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can follow the blow steps to get it:

    1. Create a measure to get the average of rainfall

    Average of rainfall = CALCULATE ( AVERAGE ( 'Table'[Rainfall] ), ALLSELECTED ( 'Table' ) )

    2. Create a calculated column to get the value which shows above or below average

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

    Best Regards

2 Replies

  • S_JB's avatar
    S_JB
    Icon for Resolver III rankResolver III
    5 years ago

    The below logic should work:

    Indicator = IF('Table'[Rainfall]>AVERAGE('Table'[Rainfall]),"Above Average",
    IF('Table'[Rainfall]<AVERAGE('Table'[Rainfall]),"Below Average",
    "Average"))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    You can follow the blow steps to get it:

    1. Create a measure to get the average of rainfall

    Average of rainfall = CALCULATE ( AVERAGE ( 'Table'[Rainfall] ), ALLSELECTED ( 'Table' ) )

    2. Create a calculated column to get the value which shows above or below average

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

    Best Regards