Max value

[eptaqlik]

Hi,

please can anyone help me?

I have value for Serie and Industry (Foglio1).

I want a report with the sum of the value for any Serie and for any Industry.

But I also want a column with the name of the Industry that have the max value for any Serie.(Foglio3)

Please see attached file with some data and the result that I want.

Many tks in advance.

Marco

[thannila]

data:

LOAD Serie,

     Industry,

     sum(Value) as value

FROM

(ooxml, embedded labels, table is Foglio1) group by Serie, Industry;

right join(data)

data1:

load Serie,

max(value) AS value

resident data group by Serie;

data2:

Generic

LOAD Serie,

   Industry,

    sum(Value) as value

FROM

(ooxml, embedded labels, table is Foglio1) group by Serie, Industry;

[sunny_talwar]

Where are you looking to see this result? In a chart or text box object?

[eptaqlik]

in a Chart

[eptaqlik]

pivot table

[sunny_talwar · Accepted solution]

Like this?

Expression used

=If(SecondaryDimensionality() = 0,

FirstSortedValue(Industry, -Aggr(Sum(Value), Serie, Industry)),

Sum(Value))

[eptaqlik]

Yes, the result is that you have explained into your picture but with this espression I have as  a result another column for all the Industry with the same name of the Industry

this is the image with my real data; I want for all the industry of the serie only the max; ex: Serie TEXPRTP7K = Valore Max GENERAL INDUSTRY

[sunny_talwar]

Would you be able to share a sample of what you have? Max Industry name needs to update for all the rows within a single serie?

[eptaqlik]

Sorry Sunny, I can't share the real data; Yes, max Industry name in all rows within a single serie because I want to define my product serie in which industry have max market

[sunny_talwar]

Try this may be

FirstSortedValue(TOTAL <Serie> Industry, -Aggr(Sum(Value), Serie, Industry))