Usin max value in a linechart

[Dwyane]

Hello all,

I have a table like this

Month Customer Name Collected Paid Actual
2 John Smith 100 1000 10%
3 John Smith 200 1050 19%
4 John Smith 300 1100 27%
5 John Smith 400 1150 35%
6 John Smith 500 1200 42%
7 John Smith 600 1250 48%
8 John Smith 700 1300 54%
9 John Smith 800 1350 59%
10 John Smith 900 1400 64%
11 John Smith 1000 1450 69%
12 John Smith 1100 1500 73%
13 John Smith 1200 1550 77%
14 John Smith 1300 1600 81%
15 John Smith 1400 1650 85%
16 John Smith 1500 1700 88%
17 John Smith 1600 1750 91%
18 John Smith 1700 1800 94%
19 John Smith 1800 1850 97%
20 John Smith 1900 1900 100%
21 John Smith 2000 1950 103%

The actual column is calculated by Collected divided by Paid. What i want to actually do is the paid value from the last month so  in this case month 21 value 1950 to calculated for each month. So for example month 2 will be 100/1950 month 3 will be 150/1950. Any idea how i can do this in a line chart?

thanks in advance

[vishsaggi]

@Dwyane you may want to try something like below:

= Sum(Collected)/Max(TOTAL [Paid Actual]) in your straight table expression. 

[MayilVahanan]

Hi 

You can use variable and store the max month value like below

vMaxMonthValue = Sum({<Month ={$(=Max(Month))}>}Paid)

So, here "=", calculates the values and store in the variable.

In the line chart, you can use like below

= Sum(Collected)/$(vMaxMonthValue)

[Vegar]

To calculate the value of the latest month you can use the firstsortedvalue(Paid,-Month) function. You will probably want to use the TOTAL keyword as well, as you do not want to calculate the value per Month in your chart. It will be like this: firstsortedvalue(TOTAL Paid, -Month).

If you have more than one lines in your chart then you will need to ignore that field in your TOTAL. Assuming you have one line per [Customer Name] it will look like this: firstsortedvalue(TOTAL <[Customer Name]> Paid, -Month).

So your final expression might look like this:

=Sum(Collected)/firstsortedvalue(TOTAL <[Customer Name]> Paid, -Month)