Usin max value in a linechart

[Dwyane]

Hello all,

I have a table like this

Month	Customer Name	Paid	Collected	Actual
1	John Smith	100	1000	10%
2	John Smith	200	1050	19%
3	John Smith	300	1100	27%
1	Kirk Polly	250	1150	22%
2	Kirk Polly	355	1200	30%
3	Kirk Polly	865	1250	69%

 The actual column is calculated by Collected divided by Paid. What i want to do is identify the max month so int examples is 3 for both customers and then use the collected amount for the max month and use that value to  divide by the paid for each month. So month 1 for John Smith would be 300/1100 month 2 would be 200/1100   Any idea how i can do this in a line chart where i plot the calculated actual using the month and customer name as dimensions.

Thanks in advance! 

[tincholiver · Accepted solution]

hi Dwyane, 

I hope this is the solution you are looking for: in the dimensions of the graph there is Customer Name and Month, in the expression
1. aggr (NODISTINCT FirstSortedValue (Collected, -Month), [Customer Name]) returns the value of the collection of the last month
2. sum (Paid) sum the payments
3. sum (Paid)
/
aggr (NODISTINCT FirstSortedValue (Collected, -Month), [Customer Name]) is the expression you need

[tincholiver]

If you put the month as a dimension first and then the name of the client you would have something like this:

[tincholiver]

Sorry, you were looking to graph the data in lines, it would look something like this