Multiple IF functions

Anonymous

I just test with the two tables you provided, they are related with Region. Since you want Q2 to calculate on qualified pipeline, I create a new qualified column which only keeps the actual qualified status for Q2, and other quarters are updated to Y.

Qualified_Update =
IF ( SEARCH ( "Q2", Table1[Fiscal Period],, -1 ) > 0, Table1[Qualified], "Y" )

We also need to create a year quarter column.

YearQuarter = 
RIGHT ( Table1[Fiscal Period], 4 ) * 100
    + MID ( Table1[Fiscal Period], 2, 1 )

Then create three measures to get the results shown in excel.

Pipeline_2 = 
VAR YearQuarter =
    CALCULATE ( AVERAGE ( Table1[YearQuarter] ) )
RETURN
    IF (
        CONTAINS ( Table1, Table1[YearQuarter], YearQuarter ),
        CALCULATE (
            SUM ( Table1[USD] ),
            FILTER (
                ALLSELECTED ( Table1 ),
                Table1[YearQuarter] = YearQuarter
                    && Table1[Qualified_Update] = "Y"
            )
        ),
        CALCULATE (
            SUM ( Table1[USD] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[Qualified_Update] = "Y" )
        )
    )

Budget_2 = 
VAR YearQuarter =
    CALCULATE ( AVERAGE ( Table1[YearQuarter] ) )
VAR Q1Budget =
    CALCULATE ( SUM ( Table2[Q1 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q2Budget =
    CALCULATE ( SUM ( Table2[Q2 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q3Budget =
    CALCULATE ( SUM ( Table2[Q3 Budget] ), ALLSELECTED ( Table1 ) )
VAR Q4Budget =
    CALCULATE ( SUM ( Table2[Q4 Budget] ), ALLSELECTED ( Table1 ) )
RETURN
    SWITCH (
        YearQuarter,
        201701, Q1Budget,
        201702, Q2Budget,
        201703, Q3Budget,
        201704, Q4Budget,
        Q1Budget + Q2Budget
            + Q3Budget
            + Q4Budget
    )

Coverage_2 = 
ROUND ( [Pipeline_2] / [Budget_2], 1 )
& "x"

Best Regards,

Herbert