locked
Powerpivot, Measure and Switch. RRS feed

  • 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]);
                  1; Sumx(filter(Database;Database[RigaDBCE]=1);Database[Month]);
                  2; Sumx(filter(Database;Database[RigaDBCE]=2);Database[Month]);
                  3; Sumx(filter(Database;Database[RigaDBCE]=3);Database[Month]);
                  4; Sumx(filter(Database;(Database[RigaDBCE]=1 || Database[RigaDBCE]=2 || Database[RigaDBCE]=3));Database[Month]);
    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

    Thank's for your kind help.

    Wednesday, July 18, 2012 7:10 PM

Answers

  • Hi. I've solved my problem.

    =if(countrows(Values(CE2[RigaCE]))=1;
           ( switch(values(CE2[RigaCE]);
                  1; Sumx(filter(Database;Database[RigaDBCE]=1);Database[Month]);

    I've created a second table CE2 which has all the lines of my P&L (so the margins too) and this table is not related to the database.

    In the first table CE, I've just kept the categories I have in my database. It works.

    Now the YTD TB has shrinked from 15mb to 3mb. It doesn't have anymore a huge list of vlookup formulas. And it doesn't even have a lot of sumif because of the margin problem.

    Now I can leverage all the other functions of powerpivot and see what I can get.

    Sad side of the story I don't think my boss in interested. And other companies too: I had a job interview today and they still have office 2003 (and the older version too on some pc...).

    • Marked as answer by Elvis Long Saturday, July 21, 2012 7:14 AM
    Friday, July 20, 2012 4:52 PM