Return values "where" condition is true

[cmccafferty]

Hi,

I am trying to get Single Prices and Twin Prices into seperate boxes. Very simple. Just a box that shows the amount.

So, at the minute I have these:

='Single Selling Price : ' & num(max ([Cost PP]), '£#,##0', '.' , ',' )

I have another table [PriceTypeDescription] that shows me the price for a single and the price for a twin. I was wondering how I can do a statement in my above formula that sort of says:

='Single Selling Price : ' & num(max ([Cost PP] where [PriceTypeDescription] = 'Single' ), '£#,##0', '.' , ',' )  so that I only get returned the single price.

Many thanks in advance

Chris

[#2 · Accepted solution]

Hi ,

       Try the below code

='Single Selling Price : ' & num(max ( { <[PriceTypeDescription] ={'Single'}> } [Cost PP] ), '£#,##0', '.' , ',' )

//Yusuf

[cmccafferty]

Sorry to bother again.. but can more than one criteria be applied? So that it works a bit like SUMIFS on Excel.

So for example:

='Single Selling Price : ' & num(max ([Cost PP] where [PriceTypeDescription] = 'Single', and [Ticket Category] = 'First Class', and [Airline] = 'Qantas' ), '£#,##0', '.' , ',' )  so that I only get returned the single price.

Many thanks

Chris

[cmccafferty]

Sorry to bother again.. but can more than one criteria be applied? So that it works a bit like SUMIFS on Excel.

So for example:

='Single Selling Price : ' & num(max ([Cost PP] where [PriceTypeDescription] = 'Single', and [Ticket Category] = 'First Class', and [Airline] = 'Qantas' ), '£#,##0', '.' , ',' )  so that I only get returned the single price.

Many thanks

Chris

[cmccafferty]

Sorry to bother again.. but can more than one criteria be applied? So that it works a bit like SUMIFS on Excel.

So for example:

='Single Selling Price : ' & num(max ([Cost PP] where [PriceTypeDescription] = 'Single', and [Ticket Category] = 'First Class', and [Airline] = 'Qantas' ), '£#,##0', '.' , ',' )  so that I only get returned the single price.

Many thanks

Chris

[#6]

HI ,

      Yes , we can apply more than one criteria .

Try  the below code .

='Single Selling Price : ' & num(max ( { <[PriceTypeDescription] ={'Single'} , [Ticket Category] = {'First Class'} , [Airline] ={ 'Qantas' } > } [Cost PP] ), '£#,##0', '.' , ',' )

Thanks & Regards

Yusuf Ali

[#7]

HI ,

     Yes , we can apply more than one criteria .

Try  the below code .

='Single Selling Price : ' & num(max ( { <[PriceTypeDescription] ={'Single'} , [Ticket Category] = {'First Class'} , [Airline] ={ 'Qantas' } > } [Cost PP] ), '£#,##0', '.' , ',' )

Thanks & Regards

Yusuf Ali

[#8]

HI ,

     Yes , we can apply more than one criteria .

Try  the below code .

='Single Selling Price : ' & num(max ( { <[PriceTypeDescription] ={'Single'} , [Ticket Category] = {'First Class'} , [Airline] ={ 'Qantas' } > } [Cost PP] ), '£#,##0', '.' , ',' )

Thanks & Regards

Yusuf Ali