Sales Amount at transaction level (aggregate at higher level) measure

[ChPetru]

Hello,

I am trying to create a measure that displays the Sales Amount at transacttion (opportunity) level, regardless of the other dimensions I pull in my report. This can be a calculated column and it will work just fine, but I need to use a parameter with this calculation in the future so it HAS TO BE A MEASURE.

When I do this calculation: 

Sales Amount Oppty Level = CALCULATE([Sales Amount],REMOVEFILTERS(Prod[ProdName]),VALUES(Oppty[OpptyName]))

i get the accurate amount at opportunity level, but, if i add dimensions to the report, I get a cartesian product (all dimension values x all dimension values).

ALLEXCEPT works only when I use the Product Table Oppty Name dimension, but I need to use Oppty Name from Product Table and the Partner dimension will make cartesian product with Opportunity for some reason. Cannot Use Oppty Name from Oppty table since then the measure will show the total sales amount for all Oppty for every row.

Sales Amount Oppty Level 2 = CALCULATE([Sales Amount],ALLEXCEPT(Prod,Prod[OpptyName]))

If i switch ALLEXCEPT to the below:

Sales Amount Oppty Level 3 = CALCULATE([Sales Amount],ALLEXCEPT(Oppty,Oppty[OpptyName]))

I will get the Sales Amount, not the Oppty total for all rows.

Please help me to see the total Oppty Amount in a visual where I can have Oppty Name, Prod Name, Partner without cartesian product, like (red is bad, since partners 1 and 2 are not connected to oppty 1):

Please help!

Here is the model

[SivaMani]

@ChPetru, It is often a tricky one. The fix would be on the model side, Changing the relationship between Oppty and Partner to a bidirectional cross-filtering. The reason is Filters applied on the Partner table won't reach Prod the table due to current relationships(Single directional). 

Hope this helps you!