Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
dharsanj
Helper II
Helper II

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

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:

 

PowerBI issue.png

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

 

0 REPLIES 0

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.