Performing calculation by taking into account the chart diminsionality

[#1]

Hi,

I have a table that the dimension of it can be changed dynamically by the user.

For example, the user can choose a Date as a dimension of the table, but he can decide that he wants the Adveriser will be the dimension (or both).

In the expression i need to calculate the ROI, which depends on Campaign.

For example, if the user choose the Date to be the dimension, then the calculation, for each row in the table, should behave as following: 

take the relevant data according to the Date, then calculate for each Campaign its ROI, and then make the average of these ROIs to ultimately get the result for this row.

I thought that aggr function will be good to use in the expression, but apparently it is not useful here:

=AVG(AGGR( Sum(Profit)/Sum(Cost), Campign))

because that the AGGR apparently creates a set, which calculate the ROIs, but with no connection to the Date dimension of the chart.

I need some expression that takes into account the table dimensionality (that, as said, could be changed by the user),and only then calculate the ROI per campaign.

I hope  the question it clear, i tried to make it so...

Thanks,

Guy

[giakoum]

Hi Guy.

not very clear but let's try.

If the dimension changes by the user, I usually store it in a variable and use it in the expressions like this :

SUM({$<[Measure Name]={"$(ConcatRegionMeasureName)"} >} [Sales])

so if you use set analysis in your expression and a variable to store dimension, maybe it will work.

[#3 · Accepted solution]

I managed to do this by using expression similar to this one:

='Sum(Aggr(Sum(PixelSum),$(=Concat(_dimension,',')&',Campaign_ID')))'

The $(=Concat(_dimension,',')&',Campaign_ID' build a string consists of the fields the user chose + Campaign_ID, so the AGGR will take place with consideration of the dimensions of the charts, and still make the calculation for each Campaign.

Thanks,

Guy