Power Pivot Principles: SWITCH with an Expression

Welcome back to the Power Pivot Principles blog. This week, we will talk about using the SWITCH function to return an Expression.

Suppose we have a daily actual and forecast Sales data table of four different products, which is then loaded into the Power Pivot Data Model:

We will define the ‘Product Name’ that matches with each ‘Product Code’, by using the SWITCH function.  The SWITCH function evaluates an expression against a list of values and returns one of multiple possible result expressions, which saves us a lot of time from using the horrible nested IF function.

We will also create the ‘Forecast Flag’ to distinguish between actual sales and forecast sales:

We want the PivotTable to display the actual and forecast sales of each product.  Therefore, we will create the ‘Product Forecast’ column which combines the ‘Product Name’ with either ‘Actual’ or ‘Forecast’.  The SWITCH function can be used to return an expression, in this case, we will use the CONCATENATE formula:

However, this is not a good way to do this, because we are repeating the CONCATENATE function, which may make the formula unnecessarily long in other examples (besides, it’s not good practice).  We can use an expression inside the SWITCH function, but in this particular example, we’d be better off “switching” the formulae around:

We can also get rid of the two unnecessary columns ‘Product Name’ and ‘Forecast Flag’. In Excel, we create a PivotTable to get Actual and Forecast Sales like the one below:

That’s it for this week!


Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.