Calculate correct overall summary revenue in DAX

Question

I have a table of data which I need to create pivot reports from, each line of data is all set out as per example below:

The overall estimated revenue is where my problem is. This is a figure for the opportunity as a whole not the individual product.

When i put this into a pivot this is the issue i am faced with:

The total should be £97.50 but as each row contains the revenue estimate it is summing it all.

Please could you advise what would be the best way to achieve the result I need. I have loaded all the data into PowerPivot and was hoping a DAX measure would be the best way to go but im struggling with the calculation for what I need to achieve.

Thanks in advance.

Answer 1

you can try this

Measure 1:=
CALCULATE (
    AVERAGE ( [Overall Opportuntiy Estimated Revenue] ),
    ALLEXCEPT ( Table1, Table1[Company] )
)

Measure 2:=
SUMX ( VALUES ( Table1[Company] ), [Measure 1] )