WEBI summing distinct values

[former_member1123229]

Dear all

Let us say I have a table as the below :

In my case if the project_id is the same , the last_total cost will always be the same. If I remove the Reference, the Last total cost will be aggregated as below :

However I wish to SUM all last total cost ONLY for distinct Project_id so in this example I would want the result to be

100 96.65

I have tried a combination of using IN clause but I can't seem to get it right. On Microsoft BI I had to create two variables but in the end I got what I wanted. Can someone pls help a newbie WEBI user pls ?

Last total cost is a measure.

Tnx

konrad

[former_member1123229]

Dear all,

Thanks for your answers.

They both work.

[amitrathi239]

other way to achieve this is:

create dimension variable on Last Total Cost object as " V Last Total Cost".

Final variable=max([V Last Total Cost]) in ([Project Id])

[Tom_N8]

Hi Konrad,

Create a variable with [Last Ttl Cost] =If(Count([Reference]) In([Project Id]) >1) Then ([Last Total Cost]/Count([Reference])) Else [Last Total Cost]

Kind regards,

Tom