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.
you can try this
Measure 1:=
CALCULATE (
AVERAGE ( [Overall Opportuntiy Estimated Revenue] ),
ALLEXCEPT ( Table1, Table1[Company] )
)
Measure 2:=
SUMX ( VALUES ( Table1[Company] ), [Measure 1] )