7 Replies Latest reply on Apr 6, 2016 12:35 AM by Andrew Watson

# Difference among members of a dimension.

Hello Community!

This is my first question ever! and I hope you do not mind if it's too simple to answer.

Say, I have a dataset, like the super store. I have a dimension that has 5 members, like sub-category in super store that has several. Now, I want to show the difference between sales of members compare to sales of the member 3. How can I create a Calculated Field that basically do this: if [sales of member 3] - [sales of member x] > 0 Then "High" Else "Low". How to denote "of" in Calculated Fields?

Any help is greatly appreciated.

Thanks!

• ###### 1. Re: Difference among members of a dimension.

Hi Sina

You could create calc fields for each member to get their sales. Using your example you could have:

Member3Sales: IF member = 3 THEN [Sales] END

MemberXSales: IF member = X THEN [Sales] END

To bring these together:

IF SUM([Member3Sales]) > SUM([MemberXSales]) THEN 'HIGH' ELSE 'LOW' END

• ###### 2. Re: Difference among members of a dimension.

How can I make it flexible and efficient for cases when I have several members within a dimension?

For instance, I put the "Sub-category" in the row shelf. Then, if I a create a parameter control to choose a member of the "Sub-category" and want to calculate the difference of sales between each member of the "Sub-category" and the chosen member in the parameter then how can I do that?

In other words, is there a way to translate this statement: " sum of sales of a member of the "Sub-category" - sum of sales of the chosen member of the "Sub-category" " into a calc fields? I created a calc fields (called Difference) in the attached workbook but it's not working the way I want, because the syntax is incorrect and I do not know how to express "of" in my syntax.

Sina

• ###### 3. Re: Difference among members of a dimension.

Hello Sina,

Haven't looked at the workbook, but this sounds very similar to a blog post Andy Kriebel just posted today

Tableau Tip Tuesday: Compare One Dimension Member to All Others With LOD Calcs

If this wasn't what you were looking for, let me know.

Regards,

Rody

• ###### 4. Re: Difference among members of a dimension.

Yes this is possible. I don't have Tableau in front of me now but you could use a parameter to define X that was mentioned in the earlier calculation:

MemberXSales: IF member = parameters.memberselector THEN [Sales] END

You would need to create that parameter 'MemberSelector' which would contain the values of member which the user should be able to select to compare with the static member.

• ###### 5. Re: Difference among members of a dimension.

Roby,

Thank you. I looked at that post and it is the exact same thing I was looking for. I am just curious that is my calc fields going to work and how can I fix that?

Regards,

Sina

• ###### 6. Re: Difference among members of a dimension.

Andrew,

I really appreciate your time. You helped a lot and I am getting this to work now. Thanks!

Sina

• ###### 7. Re: Difference among members of a dimension.

See example 6 here for a step by step breakdown using a parameter and a LOD calc. Top 15 LOD Expressions | Tableau Software

Good luck getting it to work