9 Replies Latest reply on Mar 27, 2018 5:24 PM by Patrick Myles

# Find dimension member by maximum value of measure

Hi all - I'm trying to do the following:

Pull out the dimension member name by the maximum value of given measures.  Below is an example of find the top name by the field 'active' in the recent month.  I want a table which includes the max values of other measures some of which are calculated fields

I want the table to look as follows:

Max Active = [agent 488]

Max Incoming (1Y): [agent xyz]

etc

Attached is the workbook. does anyone have a nice way to do this?

• ###### 1. Re: Find dimension member by maximum value of measure

Hi Patrick,

I find only one ID in your dataset or would you like to know the max for this ID for each of the measures?

Regards,

Norbert

1 of 1 people found this helpful
• ###### 2. Re: Find dimension member by maximum value of measure

Hi Norbert,

There are lots of IDs in the data (see screen shot).  The idea is that I want to indeed find the ID which has the maximum value of a set of measures (including calculated measures).  Does that make sense?

Patrick

• ###### 3. Re: Find dimension member by maximum value of measure

Step 1

Create a parameter

Step 2

if [Agent Name]='Active' then max([Active])

ELSEIF [Agent Name]='Incoming'

then max([Incoming]) END

Step 3

A-tleast =20

Now you can select any measure and see max values

1 of 1 people found this helpful
• ###### 4. Re: Find dimension member by maximum value of measure

Thanks.  I am not sure I followed you completely, but I found a way with your parameter solution.  I may have done it slightly differently as not sure I understand the min 20 part you had.

Attached is how I've done it thanks to your help.

The only issue now I have is that I ideally wanted to put a list of all the measures and their respective maximums for the given month in one view (without having to flip between them).

for example: in [date],

max 'active' agent = x,

max 'incoming' agent = : y.  etc.

(all in a single view)

Any ideas?

Thanks

• ###### 5. Re: Find dimension member by maximum value of measure

Hmm..not sure if there is any easy way to do that.

I managed to do below where I can highlight the top agent in any Measure. (or may be there is something we can do to below to get desired output )

If there are hundreds of agent then below may not look good but for fewer it looks OK.

Thanks,

Ritesh

• ###### 6. Re: Find dimension member by maximum value of measure

Hi Patrick,

Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.

1. NM M1. Max incoming: if [Incoming]={fixed:max([Incoming])} then ID end

2. NM M2 Max new: if [New]={fixed:max([New])} then ID end

3. NM M3 Max Active: if [Active]={fixed:max([Active])} then ID end

4. Drag the required objects to the indicated locations.

Regards,

Norbert

1 of 1 people found this helpful
• ###### 7. Re: Find dimension member by maximum value of measure

Hi  Norbert - this is great!  thank you.  I was hoping to have the actual 'name' displayed as the text (not the IDs).  If i change the calculation to show 'name' instead of ID it turns into a dimension and i can't figure out how to get into the view whilst keep the row headers?

• ###### 8. Re: Find dimension member by maximum value of measure

Hi Patrick,

Find my updated approach as reference below and stored in attached workbook version 10.5 located in the original thread

1. Define a UNION

2. NM D1 Dimension:

if [Table Name]='agents' then "Max Incoming"

elseif [Table Name]='agents1' then "Max New"

elseif [Table Name]='agents2' then "Max Active"

END

3. NM D2. Max Incoming: if [Incoming]={fixed:max([Incoming])} then [Name] end

4. NM D3. Max new: if [New]={fixed:max([New])} then [Name] end

5. Nm D4. Max active: if [Active]={fixed:max([Active])} then [Name] end

6. NM M5. Display:

if

[NM D1 Dimension]='Max Active' then [Nm D4. Max active]

elseif [NM D1 Dimension]='Max Incoming' then [NM D2. Max Incoming]

elseif [NM D1 Dimension]='Max New' then [NM D3. Max new] END

7. Drag the required objects to the indicated locations

Regards,

Norbert

• ###### 9. Re: Find dimension member by maximum value of measure

hi Norbert,

It's a very creative solution however it looks like the union will cause duplicates in the data.  if you look at the values for 'active' for example, you'll see the values has been multiplied by 3.  as my original worksheet is very complex with joins etc, i'm trying to avoid a union if possible.  Don't suppose you'd have another idea without using a join?