Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Multiple IF functions

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!

 

 

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    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

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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 :-(