# Powerpivot, Measure and Switch. • ### Question

• Hi. I'm trying to solve this problem.

I have my main database table with YTD values for my Profit and Loss Statement. There are various categories for different kind of expenses and proceeds. So for example there's the category for depreciation, sales, costs and so on. Off course I don't have categories I need to build in the P&L, like Gross Margin, Gross Profit, Ebit and so on.

I've made another table which includes the single categories in the database table and the other categories I need to calculate. I've established a relationship between the two tables.

Then I've made my pivottable with the (Categories[Categories]) as rows and I've made a measure for calculating values, exploiting the Switch function (which anyway is something amazing). So this is the formula:

```=if(countrows(Values(CE[RigaCE]))=1;
( switch(values(CE[RigaCE]);
2));0)```

CE is the table with the categories. I've set up a column with numbers in order to make the syntax more clear and easier to change, eventually.

1 is sales category and 2 and 3 variable costs categories. 4 is the Gross Maring. I don't have problem for category 1, 2 and 3: values are computed in the right way on the pivot table. The problem is for the Gross Maring: it simply disappear from the pivot table rows. But the syntax for category 4 should be correct: infact It works in other kind of measures. Where's my mistake? :P

Wednesday, July 18, 2012 7:10 PM

• Hi. I've solved my problem.

```=if(countrows(Values(CE2[RigaCE]))=1;
( switch(values(CE2[RigaCE]);