Multiple IF functions

[Anonymous]

Hi,

I'm very new to DAX functions and have been trying to combine multiple IF criterias, with 2 different data sources. The end result i'm looking for is pipeline data divided by budget by quarter as a value that shows as for example - 2x, 3x, 1.5x etc etc.. 

Pipeline X =

IF(FILTER(Pipeline,Pipeline[Fiscal Period]="Q1-2017"), sum(Pipeline[Value USD]) / sum('Targets and Actuals New'[Q1 Budget]),
IF(FILTER(Pipeline,Pipeline[Fiscal Period]="Q3-2017"), sum(Pipeline[Value USD]) / sum('Targets and Actuals New'[Q3 Budget]),
IF(FILTER(Pipeline,Pipeline[Fiscal Period]="Q4-2017"), sum(Pipeline[Value USD]) / sum('Targets and Actuals New'[Q4 Budget]),
[Q+2 Qualified Pipeline] / sum ('Targets and Actuals New'[Q2 Budget])))

The error I get is - "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Can someone please help correct this formula?

Thanks!

[Greg_Deckler]

Is this a column or a measure? Are the tables related? Can you post some sample data to recreate your data model and test?

[Anonymous]

hi @Greg_Deckler,
 
Pipeline - Table
Pipeline[Fiscal Period] - column within above table
Q2 Qualified Pipeline - Measure
 
Yes, tables are related based on the Region, product line and pipeline opportunity owner (owner info not included in sample data).
 
I have attached sample data with two tables (pipeline and budget data) and also the final view that I am looking for.
 
Hope this helps resolve my query. Thanks!

PS - I have the sample data in an excel, how do i attach it here. There does;t seem to be any option except to embed a picture 😞

[Anonymous]

Hi @Greg_Deckler

Attaching image!

[v-haibl-msft · Accepted solution]

@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

[Anonymous]

Thank you @v-haibl-msft..! Used some parts of the formulas you sent. Works now!