Comparing multiple columns and cumulative sum at max valued cell in Webi

[Private_Member_348542]

Hi All,

We have a requirement where we need to identify which all values are greater than 200 for each row, and then all those values need to be summed up and displayed at that cell which has the highest value among all columns per row. The examples are shown below.

This is the current report:

Expected report:

All I could think of right now is, that we have to implement ifelsethen logic to identify the maximum value of columns per row. Any suggestions would be much appreciated.

Thanks

[former_member198519 · Accepted solution]

Try this:

Create a formula to select the value which are greater than or equal to 200 first:

[v_lowhoigh] = =If(Not([Value]=Min([Value]) In ([Customer])) And ([Value]>=200)) Then 1 Else 0

Now use the below formula in your cross tab table:

=If(Not([Value]=Max([Value]) In ([Customer]))) Then [Value] Else(Sum([Value] Where([v_lowhigh]=1)) In ([Customer]) - Sum([Value] Where([v_lowhigh]=0) ) )

You can also do a conditional formatting to show the highest value cell which is showing the cumulative sum.