12 Replies Latest reply: Dec 1, 2016 12:27 AM by Lech Miszkiewicz

# Replace row dimension value using set analysis

Hi! I would like to ask if there's a way to change the value of a certain row dimension in a single pivot table using conditional calculations  (Set Analysis)?

Source Table:

Desired Result:

• ###### Re: Replace row dimension value using set analysis

What are initial criteria you want to apply in order to chose row with different expression? Is this always 5th row or maybe 5th, 10th, 15 th etc. etc

Just to clarify:

You want to do this in:

- Pivot Table

- with 1 dimension

- where certain row will have different expression based on values from dimensions

cheers

• ###### Re: Replace row dimension value using set analysis

Hi Lech!

Thanks for replying,criteria would depend on the value of row dimension regardless of the row position

e.g. if(Dimension='Row4', Sum({\$<Dimension={'Row 1'}>}Measure),Sum(Measure)

Regards

• ###### Re: Replace row dimension value using set analysis

Hi Jon

Yes, you can create a table like this.

I created a variable (vTEST) with "=Sum({\$<Dimension = {'Row_1'}>}Value)/Sum({\$<Dimension = {'Row_2'}>}Value)" and used "if( Dimension <> 'Row_5', Sum(Value), \$(vTEST))" as measure.

Regards

Ronny

• ###### Re: Replace row dimension value using set analysis

Hi Ronny,

Thanks for replying. I tried the solution you have suggested but seems that i didn't arrive on the answer i was looking for. Already did create a variable with the same expression and incorporate it in the expression on the measures.

Regards,

Jon

• ###### Re: Replace row dimension value using set analysis

Hi Jon

Regards

Ronny

• ###### Re: Replace row dimension value using set analysis

Hi Ronny,

Thank you for this. Will this be applicable if i have added a dimension (Month, Year) and have the variable's value depend on the added dimension column?

Thanks and  Regards,

Jon

• ###### Re: Replace row dimension value using set analysis

Hi Jon

Yes you can use with Year, Month etc..

I think you need to add a custom value to your dimension but you can do.

I just add a 'Custom' value to Month2 dimension.

Regards

Ronny

• ###### Re: Replace row dimension value using set analysis

Hi,

It all depends how your dimension will be linked with data. If there is connection between dimension and measure then going against a natural relationship may be difficult. What i did is i created dataisland field (Customer) based on my CustomerId field and then i used following formulas see picture:

table on left is having detached Customer dimension - not very efficient in terms of performance! Expression column shows you how values are calculated.

Table on right is just CustomerID (with Customer as a label) and sum(Sales)

Maybe it's not the most elegant solution but it works

cheers

• ###### Re: Replace row dimension value using set analysis

Dear Lech,

Can you please tell me what a data island does and how to do this?

Regards,

Jon

• ###### Re: Replace row dimension value using set analysis

Sure,

Data Island is a detached table in data model which is not having any relationship with rest of model.

see picture

now If you would build your expression using CustomerId and Sum(Sales) with some set analysis it would be hard to show sum of Sales for Customer A on the line directly linked to Customer B. You would have to probably use AGGR Total function. Simpler solution is to create unique values just by loading them again with Load distinct... resident etc..

Thath creates dataisland which does not have direct relationship which means that you do not have to force anything against natural relationship.

see application attached

• ###### Re: Replace row dimension value using set analysis

Hi Lech,

Thanks for this! This resolves my issue

Regards,
Jon

• ###### Re: Replace row dimension value using set analysis

No worries,

regards from Down Under

cheers