Subtotal with context operator not achieving desired result

[bassam]

Hello Experts,

I am new to learning contex operators in WebI. I have the following scenario

Dimensions: Pattern, Origin, Destination, ID, BPT Code, OPT Code.

Measures: Count, Fare

Variables Created: vCount, VFare

The scenario is that for each ID it should consider only one value in calculating vFare and vCount sum. Currently I and able to calculate data as in the actual result row. I want to achieve the result as in the expected results row.

For calculating vCount I have used this formula in sum: =Count([Ticket Count] ForEach ([ID]) ) - Result Achieved

.

For calculating vFare I have used these formula in sum:

=Sum([Fare] ForEach ([ID]) ) - Result Not Achieved

=Sum([Fare] ForEach [Pattern];[Origin];[Destination]) ) - Result Not Achieved

How can I calculate vFare so that I can get the sum 1454 for the above case?

[amitrathi239 · Accepted solution]

Foreach is not going to work in your case.ID is already in the table then Foreach is not going to create any difference. Foreach will help if ID is not included in the table but you want some calculation based on the ID.

try with these steps.

Create vraiable V RUNNING COUNT=RunningCount([Fare];([ID]))

Next variable =If([V RUNNING COUNT]>1) Then 0 Else [Fare]

if you put =sum(If([V RUNNING COUNT]>1) Then 0 Else [Fare]) it will give you the 1454

[bassam · Accepted solution]

Hi Amit,

Thank you for your answer, the above solution solves my initial question. Now considering the fact that in my data I have different values in pattern, origin and destination column and I would like to create another table to calculate the totals of vCount, vFare for each unique pattern, origin, destination while considering the fact that vCount and vFare should take only one value for the ID which are multiple as done in the Initial table. How shall I use context operator and running count function together? I am trying to achieve some thing like the below table.

Thanking you in advance.

Regards,

Bassam

[bassam]

Amit solution answerd my intital question now considering the fact that in my data I have different values in pattern, origin and destination column and I would like to create another table to calculate the totals of vCount, vFare for each unique pattern, origin, destination while considering the fact that vCount and vFare should take only one value for the ID which are multiple as done in the Initial table. How shall I use context operator and running count function together? I am trying to achieve some thing like the below table.

Can someone help me out with proper Variables/formula on how to get the desired values in vCount and vFare?

Thanking you in advance.

Regards,

Bassam