Creating a correct context to get a single ID value in calculation which is Multiple?

[bassam]

Hello Experts,

I am working on a WebI report and have the following scenario when I import all the objects in my report:

The above(Data Scenario) table is generated in the WebI report based on two tables(Table 1 and Table 2 : see image below) in the Universe joined by (table1.id=table2.id), projection function on Fare columns is set to SUM and the select statement for fare is SUM(Table1.Fare). Pattern, Origin, Destination, ID, BPT Code, OPT Code are objects of type Dimension and Fare is an object of type measure. The data in the two tables in the database appears as below:

Since the actual requirement is to have Pattern, Origin and Destination dimension objects and ID (count) and fare measures in the required table, so when i remove the ID, BPT Code and OPT code the data is projected and is shown in the table(Current Status).

In the current status table what happens is for ID which are multiple in table 2 it considers the fare value twice instead of once. I need my current status table to show me data in the ID Count and Fare columns as in the table below(Required Table) which is the correct result.

I am able to get ID Count by creating a variable"v ID count= (count(ID;distinct))" but I am unable to achieve the desired values in the fare column. I would like some help in creating the correct context for the fare column as I have tried different contexts and am unable to achieve the desired result.

Thanking you in advance,

Regards,

Bassam

[denis_konovalov]

I have fixed your tags, please select more careful next time.