Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
@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
@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 ) )
It still calculates all years. 😞
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 ) )
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 🙂
@Anonymous
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 @Anonymous
It works with me
Please check attached file
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |