cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saraalawamleh
Helper II
Helper II

Calculate Max, Min and Avg IF filter

Hello community, 

 

I've been trying to find a solution with the below scenario. I hope someone can help me.

 

Item IDYearValue
120083
120114
120107
120028
220101
220113
220155
220142

 

My data consists of multiple item ID's. Most items have multiple entries. I am trying to calculate the max, avg, and min of the value column. I have multiple scenarios:

 

1. If an item has a value in 2014 or after, then I only want to include the rows in 2014 and after.

2. If an item has no rows in 2014 or after, calculate the rows between 2003 and 2014 only.

 

For the above table, the output should be something like this:

Item 1: (2002 should not be included in the calculations)

Max: 7

Min: 3

Avg: 4.6

 

Item 2: (only 2014 and 2015 should be included in the calculations)

Max: 5

Min: 2

Avg: 3.5

 

My final output should look like this:

Row IDMaxMinAvg
1734.6
2523.5

 

I tried multiple formulas but with no luck, I keep on getting errors.

 

Please save me! 

 

 
3 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

@Saraalawamleh

 

Try this pattern

 

Avg =
IF (
    MAX ( Table1[Year] ) > 2013,
    CALCULATE ( AVERAGE ( Table1[Value] ), Table1[Year] > 2013 ),
    CALCULATE (
        AVERAGE ( Table1[Value] ),
        Table1[Year] <= 2014,
        Table1[Year] >= 2003
    )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

Hi @Saraalawamleh,

 

The solution proposed by @Zubair_Muhammad works. Maybe you need to change your "date" format.

 

 

 
Min = 
IF (
    MAX ( Data[Year] ) > 2013;
    CALCULATE ( MIN ( Data[Value] ); Data[Year] > 2013 );
    CALCULATE (
        MIN ( Data[Value] );
        Data[Year] <= 2014;
         Data[Year] >= 2003
    )
)

 

 

Max = 
IF (
    MAX ( Data[Year] ) > 2013;
    CALCULATE ( MAX ( Data[Value] ); Data[Year] > 2013 );
    CALCULATE (
        MAX ( Data[Value] );
        Data[Year] <= 2014;
         Data[Year] >= 2003
    )
)

 

test.png

 

View solution in original post

HI @Saraalawamleh

 

It works with me

 

Please check attached file

 

 

Regards
Zubair

Please try my custom visuals


View solution in original post

6 REPLIES 6
Zubair_Muhammad
Community Champion
Community Champion

@Saraalawamleh

 

Try this pattern

 

Avg =
IF (
    MAX ( Table1[Year] ) > 2013,
    CALCULATE ( AVERAGE ( Table1[Value] ), Table1[Year] > 2013 ),
    CALCULATE (
        AVERAGE ( Table1[Value] ),
        Table1[Year] <= 2014,
        Table1[Year] >= 2003
    )
)
Regards
Zubair

Please try my custom visuals


View solution in original post

It still calculates all years. 😞

Hi @Saraalawamleh,

 

The solution proposed by @Zubair_Muhammad works. Maybe you need to change your "date" format.

 

 

 
Min = 
IF (
    MAX ( Data[Year] ) > 2013;
    CALCULATE ( MIN ( Data[Value] ); Data[Year] > 2013 );
    CALCULATE (
        MIN ( Data[Value] );
        Data[Year] <= 2014;
         Data[Year] >= 2003
    )
)

 

 

Max = 
IF (
    MAX ( Data[Year] ) > 2013;
    CALCULATE ( MAX ( Data[Value] ); Data[Year] > 2013 );
    CALCULATE (
        MAX ( Data[Value] );
        Data[Year] <= 2014;
         Data[Year] >= 2003
    )
)

 

test.png

 

View solution in original post

@Arnault_

@Zubair_Muhammad

 

Thank you so much, we are almost there. I have another scenario as the following table: 

 

Row IDYearValue
320068
3200820

 

For this Row ID, the calculations are empty. There are no results for min, avg and max.

 

Almost there with your help 🙂

@Saraalawamleh

It works for me too. I just added your data in the file I shared with you.

I don't get what the problem is. The model is not changing, the measures still work.

Cheers

HI @Saraalawamleh

 

It works with me

 

Please check attached file

 

 

Regards
Zubair

Please try my custom visuals


View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors