Set analysis to get lowest sales amount for top sales person

[shnqqs]

Hi, I need to get lowest sales amount for top sales person.

Below is my expression: FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID))

However, I will only be able to get the list of lowest sales amount for each of sales person through table.

While if I implement that expression using KPI it will give the lowest sales amount among all sales person..as this is not i wanted.. I've tried to do nested aggr and firstsortedvalue but nothing is working.

I've attached screenshot for the issue for your reference. You will see in the table list its showing the correct lowest sales for each sales person but at the above total row is showing the lowest sales among all sales person.

Thanks in advance for your help!

[MayilVahanan]

HI @shnqqs 

For Total column , generally, it will display the values based on the entire chart.

In order to resolve this, you can use different expression for total column like 

If(Rowno()=0 or isnull(Rowno()), FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}, SalesPerson={"YourTopSalesPersonlogictobringthevalue"} >}LineSalesAmount),SalesPerson,ProductName,OrderID)),

FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID)))

[shnqqs]

Hi Mayil,

Big thanks to you for helping me really appreciate it!

However, I've tried using you expression its still giving the lowest sales among all of the sales person..while my concern is to find the lowest sales for top sales person.

Anyways, could you help me to understand the function for 'SalesPerson={"YourTopSalesPersonlogictobringthevalue"}  ' that you used in the expression? 

Thanks again and in advance for your help Mahil!

[MayilVahanan]

HI @shnqqs 

SalesPerson={"YourTopSalesPersonlogictobringthevalue"}  

In the place of "YourTopSalesPersonlogictobringthevalue", you need to replace the logic of "Top Sales Employee" expression.. so it will occur like  SalesPerson = {'Jamet Leverling'}

[shnqqs]

Hi Mahil,

I've replaced it with the logic, however is still giving the lowest sales of all sales person. Below is the amended expression.

If(Rowno()=0 or isnull(Rowno()), FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}, SalesPerson={"FirstSortedValue(SalesPerson, Aggr(Sum({<Year={1997}>}LineSalesAmount),ProductName))"} >}LineSalesAmount),SalesPerson,ProductName,OrderID)),

FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID)))

//FirstSortedValue(SalesPerson, Aggr(Sum({<Year={1997}>}LineSalesAmount),ProductName))

Thanks!

[MayilVahanan · Accepted solution]

Hi @shnqqs 

Try like below. 

If(Rowno()=0 or isnull(Rowno()), FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}, SalesPerson={"$(=FirstSortedValue(SalesPerson, Aggr(Sum({<Year={1997}>}LineSalesAmount),ProductName)))"} >}LineSalesAmount),SalesPerson,ProductName,OrderID)),

FirstSortedValue(TOTAL LineSalesAmount, Aggr(Sum({<Year={1997}>}LineSalesAmount),SalesPerson,ProductName,OrderID)))

If its not works, please provide the sample file

[shnqqs]

Thanks Mayil, you are a start! It works!

Just one last doubt..would  you eleborate what 'Rowno()=0 or isnull(Rowno())' is doing in the function? what is the logic being used using that?

 Thanks!

[MayilVahanan]

Hi @shnqqs 

Total will occur in Zeroth Row. So, we are replacing the total value with new expression based on our requirement.

In Pivot table, row no is null for total.

[shnqqs]

Hi Mayil,

Totally understood now! Thanks for helping!!!