Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Using the table below I am trying to create a straight table with Primary dimension of Catgory that shows me two calculations.
1. Show the value with the highest Savings - I believe I have figured this out: Max(Aggr(Sum(Savings),[Supplier],Category))
2. Show the distinct Supplier name associated with result of the above calculation.
I cannot figure out the second calculation. I have tried firstsortedvalue, minstring, maxstring with no success. Adding as a secondary dimension does not help either.
Starting with this data:
Category | Supplier | Savings |
Safety | Supplier 1 | 2342 |
Janitorial | Supplier 1 | 1231 |
Tools | Supplier 1 | 67 |
Safety | Supplier 2 | 633 |
Janitorial | Supplier 2 | 234 |
Tools | Supplier 2 | 7997 |
Safety | Supplier 3 | 7733 |
Janitorial | Supplier 3 | 23 |
Tools | Supplier 3 | 57 |
This is the Result I am looking for:
Category | Supplier | Savings |
Janitorial | Supplier 1 | 1231 |
Tools | Supplier 2 | 7997 |
Safety | Supplier 3 | 7733 |
Thanks
Try FirstSortedValue(Supplier, -aggr(sum(Savings),Supplier,Category))
Create a straight table chart with dimension Category and two expression:
=FirstSortedValue(Savings, -Savings)
=FirstSortedValue(Supplier, -Savings)
Try FirstSortedValue(Supplier, -aggr(sum(Savings),Supplier,Category))
Thanks Gysbert, much appreciated. May I ask what the ( - ) symbol in front of the aggr function does?
The minus sign negates the outcome of the aggr() function. Why do you have to do this?
Please check the HELP for FirstSortedValue():
"...Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead...."
And you are looking for Supplier with highest savings.
There is no need to use advanced aggregation (aggr() function) in your chart expression if your data table in your data model matches what you posted above, Savings already aggregated by Category and Supplier.