Complex pivot table configuration issues

[Amit_B]

Hi,

I post about this topic a while ago and some tried to help me.
I'm posting again because there is an update with more context on the issue.
This is my previous post.

As in the previous post,
I need to display Actual Vs. Budget for 3 sales measures - Number of Transactions, Items Sold and Sales Amount.
It works partially as suggested in the previous post. The problem is that I'm not getting total rows.

There are two additions:
* I need to display the data broken down by month (actual vs. budget for every month) and add o total columns for the selected period. The problem again is displaying the totals.
* I need to add a row for the GeneralCost under the totals, and add an additional total row to show the Profit (TotalSalesAmount - GeneralCost).

I attached an image (with a partial example) showing the output I'm trying to achieve.

I'd appreciate your help with building the pivot table.

[marcus_sommer]

It's not really a matter of the pivot respectively the UI in general else of the data-model - if it's native supporting the wanted views.

Personally I would create a single fact-table with the following structure:

Date   KPI    Store   Source    Value

and within the KPI are all dimensionalities like transactions, items, amounts, costs and profits included and the Source differentiate then between actuals/budgets.

With this the UI expressions are quite simple,like:

sum({< Source = {'actuals'}>} Value)
sum({< Source = {'budgets'}>} Value)
column(1) / column(2)

[Amit_B]

Thank you for the response.

Your solution could be problematic because I have many dimensions that the user can filter the data by. This means that in the script, the calculation would need to be done for every possible combination, which would increase the loading time. That's why I'm looking for an UI solution.

[marcus_sommer]

I don't see challenges with such an approach because it's simplest way to build a data-model and it supports the use of many dimensions. Further it doesn't requires to pre-calculate everything within the data-model else just to match the data into the right categories and kpi's.

All essential logic belonged into the data-model - doing it within the UI is much harder and will decrease the performance.

[Amit_B]

I created flags in the script that help with the calculation.
This still doesn't help me get the pivot result I need (see the image I attached to the question).

[marcus_sommer]

My suggestion was mainly not the creation of flags respectively to create parallel information to simplify the afterwards evaluation else to create appropriate dimensionalities.

I assume that you especially struggles with the cost and the profit part which should be artificial added to the pivot without having dimension-values for them. But this isn't possible because any calculation needs a dimensional-background against it could be performed.

Such dimension-values could be simply added in the script with something like:

concatenate(MyFacts) load pick(recno(), 'Costs', 'Profits')) as MyKPI autogenerate 2;

or within the UI with a valuelist() and within the UI are if-loops used to query these values and to branch into different calculations.

But such approaches have often significant disadvantages - therefore my suggestion to prepare the data already suitable within the data-model. Here a simplified example what's meant:

facts:
load Key, Sales as Value, 'Sales' as KPI from Sales;
concatenate(facts) load Key, Costs as Value, 'Costs' as KPI from Costs;
concatenate(facts) load Key, Sales as Value, 'Profit' as KPI from Sales;
concatenate(facts) load Key, -Costs as Value, 'Profit' as KPI from Costs;

It's very simple and performant (if the Costs are already negative values the - must not be applied). 

[Amit_B]

I've attached an image showing the current pivot I have reached.

I added an dimension (created using inline), and 8 measures with an If condition.
For example: If (dim = 'Cost', [Cost_MasterItem]).

I thought that a row filled with nulls wouldn't display, but it is still showing.
Is there a way to remove theses null rows?

[marcus_sommer]

You may look into the object-properties for settings like hide NULL/ZERO within dimension/expression. Be aware that these settings mostly only workable if they are TRUE for the entire object - means for example if an expression returned anywhere a single result the expression is shown for all dimension-values.  

[Amit_B]

I only have the checkbox 'Include zero values', and I unchecked it.
I don't have an option to ignore nulls - not in the object settings and not in the metrics.

There is an option to hide nulls in the dimensions, but it won't help because there are no null values in the dimensions.

[marcus_sommer]

I suggest you create all needed dimensionality within the data-model AND matching the data there already properly to be able to use as much as possible native out-of-the-box usability within the UI.

The fact that's possible to create the most things also within the UI and/or to apply some mixed-types does not save any efforts (usually it's the opposite) and comes often with more or less disadvantages ...