Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Set analysis to get lowest sales amount for top sales person

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.

1 Solution

Accepted Solutions
MVP

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

Thanks & Regards, Mayil Vahanan R
8 Replies
MVP

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)))

Thanks & Regards, Mayil Vahanan R
Contributor II
Author

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?

MVP

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'}

Thanks & Regards, Mayil Vahanan R
Contributor II
Author

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!

MVP

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

Thanks & Regards, Mayil Vahanan R
Contributor II
Author

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!

MVP

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.

Thanks & Regards, Mayil Vahanan R