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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.