7 Replies Latest reply: Sep 26, 2018 10:04 AM by Andrea Gigliotti

# cumulative table with sum cash applied versus total invoiced\$ per month

Dear all,

I have table with the following dimension and measurement:

Dimension:

day([Month date])

Measurement:

(Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

Table now shows:

Days is created by using dimension Day([month date])

Measurement by using:

(Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

 Days Measurement 1 6 2 3 3 5 4 6 5 8 6 8 7 4 8 3 9 6 10 7 11 8 12 8 13 8 14 8 15 9 16 9 17 6 18 4 19 3 20 2

Now I want to see this result as cumulative.

 Day Cumulative 1 6 2 9 3 14 4 20 5 28 6 36 7 40 8 43 9 49 10 56 11 64 12 72 13 80 14 88 15 97 16 106 17 112 18 116 19 119 20 121

How do I need to do this?

Please help me.

Ugur

• ###### Re: cumulative table with sum cash applied versus total invoiced\$ per month

Try range sum with above e.g.

rangesum(above(Sum(LineSalesAmount),0,RowNo()))

• ###### Re: cumulative table with sum cash applied versus total invoiced\$ per month

Dear Dilip.

Thank you very much for your answer,

Would it possible for you to write the formula with using my example?

Thanks

Ugur

• ###### Re: cumulative table with sum cash applied versus total invoiced\$ per month

just replace the sum function by yours

rangesum(above(

(Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])

/

Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

, 0,RowNo() ) )

• ###### Re: cumulative table with sum cash applied versus total invoiced\$ per month

Hi Ugur,

You can try this:

1. Create a variable v_Measurement

```(Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])
/
Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))

```

2. Using the RANGESUM function in combination with the ABOVE function to accumulate the values you need.

Create Measure Accumulate:

```rangesum( above( \$(v_Measurement),0,rowno()))

```

3. Create Bar Chart or Table with

Dimension: day([Month date])

Measure: Accumulate

Hope this helps,

Justin.

• ###### Re: cumulative table with sum cash applied versus total invoiced\$ per month

Dear Justin,

Thank you very much for your reply, appreciate it. Unfortunately the outcome is '-'.

Can you please write the exact formula with using my example?

Thanks

Ugur

• ###### Re: cumulative table with sum cash applied versus total invoiced\$ per month

Hi Ugur,

Pls try this:

```rangesum( above( (Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'}>}[Cash App Amt USD])
/
Sum({<[Year Month]={'\$(Current_Month_YYYYMM)'},[Trans Type]={'invoice'}>}total <[Year Month]>[Trans Amt USD]))
,0,rowno()))
```

I think If your current Measure is working so the Measure Accumulate is working too

p/s: The Measure Accumulate like this: rangesum( above( sum([Expression]),0,rowno()))

Thanks,

Justin.

• ###### Re: cumulative table with sum cash applied versus total invoiced\$ per month

Let's try replacing RowNo() with RowNo(TOTAL).

I hope it helps.