I am really stuck here and I hope, you can help me out.
So as you can see in the pictures I am trying to make an pivot table, that has all the relevant numbers of an bussines like new orders (Auftragseingang), sales(Umsatz) and profit(Ergebnis). Relative to the name in the first column I want to see the different numbers for every row. So for example second row new orders(Auftragseingang). Thats what the if-function "title" is for. Also in every column there are different numbers. For example last year, this year or budget. This is what the "cat='VJ MS'" is for.(Last years numbers).
Even it is a huge formula it did work out well, until I now also want to see for example profit/sales in percent. This is the last row of the formula and its not working at all. It even destroys all the other numbers. Maybe somebody understood my problem and helps me to make an smaller but more efficient formula?
This is indeed a rather frustrating problem with P&L and similar reports.
My recommended approach is to create a mapping file (I use Excel) with the names of the rows you want in the dimension and the actual data they should have, e.g.
Note the addition of a NUM field (to make sure the rows are in the right oder), STYLE (for bolding / backgrounds), and ISPERCENT (I use this to determine whether a row is a sum or a percent-of-sales value). CATEGORY is the actual data that should be associated with my synthetic (PNL_) rows.
Your formula would then be something along the lines of (I'm freehand writing this, so it may not be entirely accurate):
The Num() is necessary to format the expression as a percent or a number depending on the line type. The first half of the formula deals with regular sum values while the latter deals with percent values (sum of the current row - which may contain multiple categories - divided by total sales in my example, but your situation may differ).
Unfortunately - and this is where it starts getting ugly most of the time - these reports tend to have a large number of expressions (in your example you're showing 17 of them), and each requires additional set analysis to get the correct data - so you'll have to adapt the base formula for each expression.
I hope this helps - I am not able to include my sample file as scrambling the data without breaking the actual example would be very time-consuming.
I think the problem is, that expressions like 'Auftragseingang', VSP in % and all the titels are only there, so the programm can match it with the first column of the table, and select the right data for each row.
But maybe I am just misunderstanding something.
I tried somethin else (it is even more complex) and now only the number format is wrong.
The percentage shows 0,038% but it should show 38% ??
The problem is, that I cant show you the table because its confidential
=num(sum(if(Titel='Auftragseingang' and cat='VJ MS', [AE_Gesamt],
if(Titel='Umsatz' and cat='VJ MS',[Umsatz_Gesamt],
if(Titel='Auftragsbestand' and cat='VJ MS', [AB Gesamt],
(sum(if(cat='VJ MS'and Titel='VSP in %', VSP_Gesamt,
if(cat='VJ MS'and Titel='Bruttoergebnis in %', [Bruttoergebnis absolut],
(sum(if(cat='VJ MS' and Titel='VSP in %', Umsatz_Gesamt,
if(cat='VJ MS' and Titel='Bruttoergebnis in %', Umsatz_Gesamt,