Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
water-guy-5
Helper III
Helper III

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!

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

Hi @water-guy-5 ,

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"
    )
)

yingyinr_0-1627286875692.png

Best Regards

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

View solution in original post

2 REPLIES 2
v-yiruan-msft
Community Support
Community Support

Hi @water-guy-5 ,

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"
    )
)

yingyinr_0-1627286875692.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
S_JB
Resolver III
Resolver III

The below logic should work:

Indicator = IF('Table'[Rainfall]>AVERAGE('Table'[Rainfall]),"Above Average",
IF('Table'[Rainfall]<AVERAGE('Table'[Rainfall]),"Below Average",
"Average"))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.