exceldaxoffice365powerpivot

Calculate correct overall summary revenue in DAX


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: Example Data Table

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: EXAMPLE 2 PIVOT

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.


Solution

  • you can try this

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

    enter image description here