Power BI Total Rows Behavior

The behavior you are experiencing is related to the default summarization properties of Power BI. When using measures in a table visualization, Power BI automatically creates a 'Total' row at the bottom, which aggregates the measure based on its mathematical operation. If the measure is a sum, it will sum all the values. If the measure is an average, it will calculate the average, and so on.

This can result in misunderstandings, particularly when using multiple measures. The 'Total' row behaves differently than rows within the table - it applies the mathematical operation (sum, average, etc) directly on the data defined in the measure, rather than 'summarizing' the rows above, like you might expect when looking at the table. Hence, it's usually a single total row rather than totaling per column.

There isn't a direct functionality to change this behavior. However, you can distinguish total row calculations from the row level computations through conditional formatting or by creating separate measures.

Here's an example of how to do it:

Measure Totals = IF(
    ISINSCOPE('YourTable'[YourDimension]), 
    [YourMeasure], 
    BLANK()
)

This DAX formula checks whether 'YourDimension' from 'YourTable' is in scope. If it is (meaning the calculation is at the row level), it returns the value of 'YourMeasure'. If it's not in scope (meaning it's the Total row), it returns BLANK(). You need to create such measures for each column you want to display a total for.

This is not a perfect solution, and it can make your measures more complex with additional checks. Depending on the complexity of the visual, it may also affect performance, so it's recommended to use this technique judiciously.

For deeper understanding of how DAX calculations work, I highly recommend going through some training courses on the Enterprise DNA Platform. The platform offers a comprehensive set of materials that can help you master DAX and Power BI.