Help with KPI's and Aggregation functions

[Ratier]

I want to create a KPI Panel but I need some help to do so. In a table similar to the one below:

Sales Nº	Month	Volume Sold	Financial profit/loss
1	Jan	500	$ 2,000.00
2	Jan	520	$ 1,200.00
3	Jan	530	$ 2,200.00
4	Feb	490	$ 8,000.00
5	Feb	470	$ 3,200.00
6	Feb	580	$ 3,100.00
7	Feb	620	$ 1,600.00
8	Feb	200	- $ 1,000.00
9	Mar	150	- $ 500.00
10	Mar	700	$ 3,000.00

I'd like a way to express the following data (not only the number but also the corresponding Month)

• Most sales made in a month: 5 Sales; February 
• Most profitable sale: $ 8,000.00; 4th sale
• Greates financial loss: - $ 1,000.00; 8th sale
• Most profitable month: $ 14,800.00; February
• Lowest profitable month: $ 2,500.00; March

I already know how to use, for exemple, Max() function to obtain the most profitable sale but I don't know how to express not only the number but the corresponding row of the number (the correspondig sale nº). I also know how to use the count() function for the whole table but not for each month and only then getting the max() of the previous calculation.

i'd like some help with that, please. Thank you in advance

[cristianj23a · Accepted solution]

Hello, I send you the code, with that what you require is fulfilled.

1.- Most sales made in a month

Count({< Month = {"=Aggr(Rank(MaxString([Financial profit/loss]), Month), Month) = 1"} >} [Sales Nº]) & ' Sales in ' & MaxString({< Month = {"=Aggr(Rank(MaxString([Financial profit/loss]), Month), Month) = 1"} >} Month)

2.- Most profitable sale

=Num(Max([Financial profit/loss]), '$#,##0.00') & '; ' &
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 1, '1st',
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 2, '2nd',
If(MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 3, '3rd',
MaxString({< [Financial profit/loss] = {"=Max(TOTAL [Financial profit/loss])"} >} [Sales Nº]) & 'th Sale')))

3.- Greates financial loss

=Num(Min([Financial profit/loss]), '$#,##0.00') & '; ' &
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 1, '1st',
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 2, '2nd',
If(MinString({< [Financial profit/loss] = {"=Min(TOTAL [Financial profit/loss])"} >} [Sales Nº]) = 3, '3rd',
If(Rank([Financial profit/loss]) = 1,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'st sale',
If(Rank([Financial profit/loss]) = 2,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'nd sale',
If(Rank([Financial profit/loss]) = 3,
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'rd sale',
'$' & Replace(Num(Min([Financial profit/loss]), '#,##0.00'), ',', '.') & '; ' & [Sales Nº] & 'th sale'
))))))

4.- Most profitable month

=Num(Max(TOTAL Aggr(Sum([Financial profit/loss]), Month)), '$#,##0.00') & '; ' &
MaxString({< [Financial profit/loss] = {"=Max(TOTAL Aggr(Sum([Financial profit/loss]), Month))"} >} Month)

5.- Lowest profitable month

=Num(Min(TOTAL Aggr(Sum([Financial profit/loss]), Month)), '$#,##0.00') & '; ' &
MaxString({< [Financial profit/loss] = {"=Min(TOTAL Aggr(Sum([Financial profit/loss]), Month))"} >} Month)

Here the test table in script:

Table: 

LOAD * INLINE [
Sales Nº, Month, Volume Sold, Financial profit/loss
1, Jan, 500,2000.00
2, Jan, 520,1200.00
3, Jan, 530,2200.00
4, Feb, 490,8000.00
5, Feb, 470,3200.00
6, Feb, 580,3100.00
7, Feb, 620,1600.00
8, Feb, 200,-1000.00
9, Mar, 150,-500.00
10, Mar, 700,3000.00
];

Results:

Regarts.

[vinieme12 · Accepted solution]

Just use FirstSortedValue()

• Most sales made in a month:
• =FirstSortedValue( aggr(MonthNm &'-' & num(count(SalesNo),'#,##0'),MonthNm)&' Sales' , -aggr(count(SalesNo),MonthNm))
•  
• Most profitable sale:
• =FirstSortedValue( aggr(SalesNo&'th Sales -' & num(sum(profit_loss),'$#,##0.00'),SalesNo) , -aggr(sum(profit_loss),SalesNo))
•  
• Greates financial loss:
• =FirstSortedValue( aggr(SalesNo&'th Sales -' & num(sum(profit_loss),'$#,##0.00'),SalesNo) , aggr(sum(profit_loss),SalesNo))
•  
• Most profitable month: 
• =FirstSortedValue( aggr(MonthNm&' -' & num(sum(profit_loss),'$#,##0.00'),MonthNm) , -aggr(sum(profit_loss),MonthNm))
•  
• Lowest profitable month: $
• =FirstSortedValue( aggr(MonthNm&' -' & num(sum(profit_loss),'$#,##0.00'),MonthNm) , aggr(sum(profit_loss),MonthNm))

Load * inline [
SalesNo,MonthNm,VolumeSold,profit_loss
1,Jan,500,2000.00
2,Jan,520,1200.00
3,Jan,530,2200.00
4,Feb,490,8000.00
5,Feb,470,3200.00
6,Feb,580,3100.00
7,Feb,620,1600.00
8,Feb,200,-1000.00
9,Mar,150,-500.00
10,Mar,700,3000.00
];
exit Script;