Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try range sum with above e.g.
rangesum(above(Sum(LineSalesAmount),0,RowNo()))
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.
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
Dear Dilip.
Thank you very much for your answer,
Would it possible for you to write the formula with using my example?
Thanks
Ugur
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.
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() ) )
Let's try replacing RowNo() with RowNo(TOTAL).
I hope it helps.