Replace row dimension value using set analysis

[Anonymous]

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:

Thanks in advance

[Lech_Miszkiewicz]

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

looking forward to your reply

cheers

[Anonymous]

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

[r_wroblewski]

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

[Lech_Miszkiewicz]

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

[Anonymous]

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

[Anonymous]

Dear Lech,

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

Regards,

Jon

[r_wroblewski]

Hi Jon

please use a "="sign inside variable.

Regards

Ronny

[Lech_Miszkiewicz · Accepted solution]

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

[Anonymous]

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