6 Replies Latest reply on Jan 31, 2019 9:53 AM by Ken Flerlage

# Show max on an aggregated field

Hi,

I have a requirement where I need to show the max of a measure with other dimensions in the view.

How do I achieve this?

Attached is the screenshot. For 'Blue Book' I just need to show the max of failure rate i.e. 30%.

There are multiple values for 'training campaign' field.

• ###### 1. Re: Show max on an aggregated field

Hard to answer without seeing a workbook. Any chance you could attach one?

• ###### 2. Re: Show max on an aggregated field

Unfortunately I don't have the workbook.

Just to give more insight -

There are 2 dimensions and the failure rate is calculation (sum([incorrect answers])/sum([no of attempts]).

Now the requirement is just to show the records with the highest failure rate.

I did try writing LOD expression but still could not achieve the result.

• ###### 3. Re: Show max on an aggregated field

Could you create a mockup of the workbook and attach it?

• ###### 4. Re: Show max on an aggregated field

Do you need to keep the other dimensions in the view, particularly Question and Answer? If not, then you can remove them and simply change the SUM(F4) to MAX(F4). But, if you need to keep those in the view, you can use an LOD:

Max Value

// Get the max percentage fore each training campaign.

{FIXED [Training Campaign]: MAX([F4])}

See attached.

• ###### 5. Re: Show max on an aggregated field

I created this calculated field --

{FIXED [Training Campaign]: max( { FIXED [Training Campaign],[Question],[Answer] : ( (sum([Incorrect Answers]) / sum([Number of Attempts])) ) } ) }

I get the max of failure rate for every training campaign but how do I get rid of the rows. I just need to show the rows with maximum failure rate.

• ###### 6. Re: Show max on an aggregated field

I don't have your actual workbook, so I'll show you with the sample you gave. You'd need to create another LOD to check the value to see if it matches the max:

Question with Max?

// Is the the record with the max value?

{FIXED [Training Campaign], [Question] : MAX(IIF([F4]=[Max Value], "Y", NULL))}

Then filter on that field, only keeping the "Y" values.