Issue with cumulative Sum

[sureshbaabu]

Hello All,

I'm having an issue while calculating the cumulative sum.

I was able to create the cumulative% and the results are as expected when there is no selection made.

however, on any selection the cumulative  value changes.

I believe the issue may have occurred since I use a 'Before' function in the calculation. As the number of records comes down when a selection is made.

In my attached example, I have built a chart with actual and cumulative.

wanted to make a selection on the group, and that should not be the cumulative value, but filter the resources associated with the group.

Could someone please share your thoughts?

Thanks

[sunny_talwar]

In my attached example, I have built a chart with actual and cumulative.

wanted to make a selection on the group, and that should not be the cumulative value, but filter the resources associated with the group.

What does this mean? What is the desired output based on selections and when nothing is selected?

[sureshbaabu]

Please refer the attached.

I would like to retain the Cumulative sum that was initially derived, even when a selection is made on the column 'Group'. But Currently, the Cumulative sum changes as I make a selection on the column Group.

[sunny_talwar]

Check now

[sureshbaabu]

Thank you!! that worked as expected.

Could you please share your thoughts on the below -

1. If a resource does not have an entry for a month. the expression you suggested retuns null, but it should be showing up the cumulative value

Note: I removed the record with period '01/03/2017' for Resource5, from the data file shared initially

2. the total does not populate for cumulative.

[sunny_talwar]

1) Would you be able to share a sample where you modified the data to have null?

2) What do you expect a total of cumulative to be? the last value or something else?

[sureshbaabu]

1. I have uploaded the new qvw to the discussion. please check.

2. Cumuliative of all the resources by Month (the same way on how the total of actual is calualted)

[sunny_talwar]

Here is the solution to your first issue

(rangesum(Before(sum({<[Task Type]={"project"}>}[Hours Billed]),0,RowNo())))

/(rangesum(Before(sum([Hours Billed]),0,RowNo())))*avg(TOTAL <[Resource Name]>1)

2) I am still not sure what you mean, what numbers do you expect to see for the sample? Logic can you explain the logic also?

[sureshbaabu]

Thanks for the solution. That's cool

For the second issue:

I have uploaded the new file.Please check the cumulative total on the chart 'Month View'.

Jan-> 70%

Feb->76%

Mar->77%

Thank you!!

[sunny_talwar]

One solution

=If(Dimensionality() = 2,

(rangesum(Before(sum({<[Task Type]={"project"}>}[Hours Billed]),0,RowNo())))

/(rangesum(Before(sum([Hours Billed]),0,RowNo()))),

Aggr((rangesum(Above(sum({<[Task Type]={"project"}>}[Hours Billed]),0,RowNo())))/(rangesum(Above(sum([Hours Billed]),0,RowNo()))), Period))