Display '$' symbol to only highest sales amount on condition.

[#1]

Hello all,

The possible output should be the following.

Name	Sort order	Sales Amount	Symbol
Mike	3	13000	$
Jim	3	400
Robert	3	200
James	4	16000
Robert	4	700
Jack	5	14000
Kevin	5	1000

Description of the issue:

The data contains only following fields.

1.      Name

2.      Sort Order

3.      Sales Amount

We want to add a dollar symbol “$” sign at the 13,000 as it is the highest amount in the lowest sort order #

PFA for sample database.

Any help would be appreciated.

Thanks in Advance!

The task can be shown in with straight table or table box.

BR

Susvith

[sunny_talwar · Accepted solution]

You can try this expression in the straight table:

=If(SortOrder = Min(TOTAL SortOrder) and [Sales Amount] = Max(TOTAL <SortOrder>[Sales Amount]), '$')

Dimension

Name

SortOrder

[maxgro]

try this in a straight table with Name and SortOrder as dimensions

if(rank(total sum({$ <SortOrder={"$(=min(SortOrder))"}>} [Sales Amount]))=1, '$', '')

[sunny_talwar]

Or this for the script to display it in a table box object:

Table:

LOAD Name,

    SortOrder,

    [Sales Amount]

FROM

[Book1 (8).xlsx]

(ooxml, embedded labels, table is Sheet2);

Left Join (Table)

LOAD *

Where RecNo() = 1;

LOAD SortOrder,

  Max([Sales Amount]) as [Sales Amount],

  '$' as Symbol

Resident Table

Group By SortOrder

Order By SortOrder;

[sunny_talwar]

A simpler script would be this:

Table:

LOAD Name,

    SortOrder,

    [Sales Amount]

FROM

[Book1 (8).xlsx]

(ooxml, embedded labels, table is Sheet2);

FinalTable:

LOAD *,

  If(RowNo() = 1, '$') as Symbol

Resident Table

Order By SortOrder, [Sales Amount] desc;

DROP Table Table;