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.
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!
Solved! Go to 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
Is this a column or a measure? Are the tables related? Can you post some sample data to recreate your data model and test?
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
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
Thank you @v-haibl-msft..! Used some parts of the formulas you sent. Works now!
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |