This seems to be a data quality issue.
The problem is that the calculation that you created is also applied at the Grand total level. Thus the aggregate Product Gross Value (221) is divided by the aggregate count of Booking ID (12) and multiplied by the aggregate distinct count of Sales Invoice ID (22).
If is is a one-off anomaly, you can try this formula
when 200204397 then 11
This will assign a value of 11 (instead of 22) to sales invoice 200204397.
How about this:
1. place "SalesInvoiceID" on the Level of Detail shelf
2. create a calculated field with a formula of:
IF FIRST()==0 THEN WINDOW_SUM(AVG([ProductGrossValue]),0,IIF(FIRST()==0,LAST(),0)) END
3. place this calc field on your text shelf instead of your "Calculation1"
4. set this pill's compute using to "SalesInvoiceID"
Is that what you are looking for?
Hi Thanks to both for your help. Joe, your formula works perfect and gives me the result im looking for. Many thanks again. Can you explain to me how this formula works? Im trying to understand it but having difficulty.