Find max Value

[#1]

Hi All,

How can i find a max value for the below Scenario:

Scenario: All i want to do here under which head of service there is max spend for a supplier from the data set below.

For reference i have also added output i am trying to achieve

Supplier Name	Spend	Head Of Service
Supplier 1	200	Service 1
Supplier 1	300	Service 1
Supplier 1	500	Service 1
Supplier 1	600	Service 2
Supplier 1	600	Service 2
Supplier 1	600	Service 3
Supplier 2	500	Service 1
Supplier 2	600	Service 2
Supplier 3	600	Service 1
Supplier 3	600	Service 3

Output
Supplier Name	Head Of Service	Total
Supplier 1	Service 2	1200
Supplier 2	Service 2	600
Supplier 3	Service 3	600

I have tries using pivot and straight etc but nio luck,Please can someone help me with this.?

jagan please help

Regards,

Karhik

[dclark0699]

Here's one possible straight table solution

Dimension = Supplier Name

Expression 1 =MAX(AGGR(SUM(Spend),[Supplier Name],[Head Of Service])) //This will give you the highest spend total

Expression 2 =FirstSortedValue(DISTINCT [Head Of Service],-AGGR(SUM(Spend),[Supplier Name],[Head Of Service]))

[jagan]

HI,

Try like this

Dimension:

Supplier Name

=Aggr(If(Rank(Sum([Spend])) = 1, [Head Of Service]), [Supplier Name], [Head Of Service])     ---- (Calculated Dimension)

Expression:

=Sum([Spend])

Note: Select Supress Null value option in Dimension tab for Calculated Dimension

Hope this helps you.

Regards,

Jagan.