Pivot Table : personalised dimension

[clarachac]

Hello,

I have a pivot table with a master item hierarchical dimension (countries>cities) and different measures : 

I would like to create a dimension named 'Mondial' which doesn't take into consideration the filters and the measures = total of the other dimensions (= sum of countries)

So if I filter on 'Espagne' for example, I want to have a pivot table with the column 'Mondial' for which the measures haven't changed and a column 'Espagne' for which the measures correspond to the country chosen.

[gomeri]

Hi @clarachac,

in measures, in set expression of measure, use as identifier "1":

For ex. instead use this "=SUM(MEASURE1)" you can use "=SUM({1}MEASURE1)". 

It change set identifier of your set expression and when you take a selection of a country, this measure remain unvariable. 

Hope i help you with your question.

Thanks

[sbaro_bd]

Hi @clarachac ,

@gomeri provide a good solution but the measure "remain unvariable" for ALL filters in your application. If you want a result with only disconnection from countries and cities, try someting like this : 

SUM({<Countries=, Cities=>} TOTAL MEASURE1)

Regards.

[pedrohenriqueperna · Accepted solution]

Hi,

You could probably add a 'Mondial' value to the existing dimension in your loader. There you could already set the values as the sum for all countries, or you could leave it blank and set in the expression something like:

If(yourDimension = 'Mondial', Sum({1} Total value)

The {1} will ignore every filter you choose. If you wish to respect some of the filters, then you can add them as exceptions like:

If(yourDimension = 'Mondial', Sum({1<year =$:: year, month =$:: month>})

This would ignore every filter, except for year and month.