4 Replies Latest reply on Nov 26, 2018 4:47 AM by seraj alam

# Sort a tabular chart with 4 dimensions dynamically based on selected measure

Hi All,

i want to sort the chart with the dimensions in order of  segment->category->sub-category->state . also it should be flexible to select the measure to use for sorting.

basically first the categories withing the segment should be sorted. then the subcategories withing the category should be sorted then the states within the sub category should be sorted. Please let me know the most efficient way to do this.

Regards,

Philip

• ###### 1. Re: Sort a tabular chart with 4 dimensions dynamically based on selected measure

Like this ?

• ###### 2. Re: Sort a tabular chart with 4 dimensions dynamically based on selected measure

Hi Philip,

Not sure but find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

1. Define Parameter P1. Select Measure

2. Define M1. Measure: if [P1. Select Measure]='Profit' then [Profit]

elseif [P1. Select Measure]='Quantity' then [Quantity]

elseif [P1. Select Measure]='Sales' then [Sales]

END

3. Define M2. Rank Unique: RANK_UNIQUE(sum([M1. Measure]))

4. Drag M2. Rank Unique on the rows shelf, set to Discrete, and move between Category & Segment

5.  Edit table calculation M2. Rank Unique

Hope it helps,

Regards,

Norbert

• ###### 3. Re: Sort a tabular chart with 4 dimensions dynamically based on selected measure

My solution is:

1. Create a parameter "Sort by" having Sales. Profit and Quantity as String values.

2. Create a calculated field by "Sort by" calculated field as :

RANK(

SUM(

IF [Sort By] = "Sales" THEN [Sales]

ELSE [Profit]

END

)

)

3.Pull the fields as in the attached screenshot.

Do not forget to change the Sort By calculated field to Discrete.

4. Once you have the view, open Edit table calculation for the Sort by calculated field. Select Pane(down) and in Specific Dimensions, select Category and Sub Category.

Untick Show header to hide the Rank.

5. Show parameter control.

Hope this helps.

• ###### 4. Re: Sort a tabular chart with 4 dimensions dynamically based on selected measure

Hi Philip,

Please refer to below link hope this is what you are looking for.