Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]))
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.