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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

Regards
Zubair

Please try my custom visuals

View solution in original post

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

 

test.png

 

View solution in original post

HI @Anonymous

 

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

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

Regards
Zubair

Please try my custom visuals
Anonymous
Not applicable

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

 

test.png

 

Anonymous
Not applicable

@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 🙂

@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

 

 


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.