3 Replies Latest reply on Oct 5, 2016 11:13 AM by Adam Erdesky

# How to get the dimension value associated with the max, e.g., of a measure?

Say I've got the following result in a worksheet (from the Superstore dataset):

SegmentSUM(Sales)
Consumer1,161,401
Corporate706,146
Home Office429,653

...but that I want to add another worksheet to contain a short piece of prose to enhance the dashboard and aid understanding by the end user. On this second worksheet, I want to use a calc field to create the sentence, "The Consumer segment is associated with the largest sales volume (\$1,161,401)."

How can I get the dimension value "Consumer" using functions?

"The " + <associated dimension value> + " segment is associated with the largest sales volume (\$" + str(round(WINDOW_MAX(sum([Sales])),0)) + ")"

Or--is there a better approach altogether?

Thanks!

• ###### 1. Re: How to get the dimension value associated with the max, e.g., of a measure?

Hi Adam! You could just duplicate your existing sheet and edit the Label mark (using the "..." button when you click on the Label mark) so it has the desired text.

For bonus points, you could put this all on the same sheet if you change SUM(Sales) to a discrete pill.

Note, however, that this won't allow you to change the rest of the blurb to reflect the reality of the dimension, e.g. which one is largest or smallest in volume. You'll have to add more calculated fields to do that.

1 of 1 people found this helpful
• ###### 2. Re: How to get the dimension value associated with the max, e.g., of a measure?

Is that what you wanted? Using RANK() you can get the top segment.

Compute using Segment on Top Segment field.

Let me know If you've any query.

Mahfooj

2 of 2 people found this helpful
• ###### 3. Re: How to get the dimension value associated with the max, e.g., of a measure?

Just what I was looking for--thanks Mahfooj!