cancel
Showing results for
Did you mean:
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 ID Year Value 1 2008 3 1 2011 4 1 2010 7 1 2002 8 2 2010 1 2 2011 3 2 2015 5 2 2014 2

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 ID Max Min Avg 1 7 3 4.6 2 5 2 3.5

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

3 ACCEPTED SOLUTIONS
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

Resolver II

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

Community Champion

It works with me

Regards
Zubair

6 REPLIES 6
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

Helper II

It still calculates all years. 😞

Resolver II

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

Helper II

@Arnault_

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

 Row ID Year Value 3 2006 8 3 2008 20

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

Almost there with your help 🙂

Resolver II

@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

Community Champion

It works with me

Regards
Zubair

Announcements