Forum Discussion
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.
Please save me!
Anonymous
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 ) )Hi Anonymous,
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 ) )HI Anonymous
It works with me
Please check attached file
6 Replies
- Zubair_Muhammad
Community Champion
Anonymous
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 ) )
Arnault_Resolver III
Hi Anonymous,
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 ) )