Problem with Semi-additive measure - how to avoid aggregation in the Total row in a Matrix view

[dharsanj]

Hi,

I have an issue in computing data values in a hierarchy where certain columns need to be aggregated respecting the hierarchy, and certain columns should not. The problem statement is best explained via an Excel visual of what is happening and what I want to happen:

The report I intend to produce is in Columns B - J. I am able to produce Columns B - G correctly, but I get the values in Cols L - N instead of the Desired values in H-J

The Geo hierarchy is as follows: Region -> Region (by North/South) -> Cities

The visual is setup as a matrix, where Totals are computed at the region hierarchies. 

The column descriptions are as follows:

• Return column is calculated using  a measure and it aggregrates correctly at the respective Region hierarchies
• Veterance investment is also calculated using a measure which aggregrates correctly at the respective Region hierarchies
• Rookie investment is also calculated using a measure which aggregrates correctly at the respective Region hierarchies
• Rookie Ramp comes from a table. Region North, Region South, and Total Region values (highlighted in yellow) come from a table and should not be aggregated. However, when I put them in the PowerBI visual, Column L shows how they come out, where aggregation is applied (Noted as (1), (2), (3)).

The rest of the columns are defined via formulas:

• Investment = Veteran Investment + Rookie Investment * Rookie Ramp
• ROI = Return/Investment

Because of the aggregation happening on the Rookie Ramp column, the computed Investment and ROI are wrong in the TOTAL rows. 

For example, the Region South Total Investment is calculated using the formula:

34 + 14 * 100% = 48 instead of using the (right) formula:

34 + 14 *   42% = 40

Any tips in how I can avoid the aggregation on the Rookie Ramp column via the appropriate DAX construct? 

Thanks in advance