How to replace a category line in matrix by a aggregation?

[tahechadv_2022]

Hi guys

I have this matrix above, which is a DRE in portuguese or Income Statement in english.

This income statement in the "003" line, needs to aggregate the line (001 + 002 = 003). (In this case 10 - 1 = 9, so the 003 should receive the 9 value)
The measure 2024 is a measure SUM(table[valor])

So, There are no values to sum in the column table[valor] when the line is 003, because 003 is a aggregation as I said before of the sum of the lines 001 + 002.

There are still other categories to aggregate like this one, but if I get the logic to do so, it will be easy. I already tried to use SWITCH and IF to solve this problem but all didn't succeeded in any of them, even using chagpt, claude, gemini.

resuming this problem involves 3 columns:
parte1 - which have the values (001, 002, 003...)

Classificação - which is a created column from a CONCAT(), shown in the print screen

value - self describe

Thank-you.

[Bibiano_Geraldo · Accepted solution]

Hi @tahechadv_2022 ,

You can achieve your goal by this DAX measure:

Measure_Aggregation =
VAR RowID = SELECTEDVALUE( table[parte1] )
RETURN
    SWITCH(
        TRUE(),
        RowID = "001", SUM( table[valor] ), -- Return value for row 001
        RowID = "002", SUM( table[valor] ), -- Return value for row 002
        RowID = "003", 
            CALCULATE( 
                SUM( table[valor] ), 
                FILTER( ALL( table ), table[parte1] IN { "001", "002" } ) 
            ), -- Sum for row 003
        BLANK() -- Default for other rows
    )

[SacheeTh · Accepted solution]

Hi @tahechadv_2022,

You may try @Bibiano_Geraldo 's same query as followin, I revised a bit

To ensure that your measure works for aggregations (like rows 001, 002, and 003) across all levels of your hierarchy (e.g., year, quarter, month, day), you'll need a more dynamic DAX measure that calculates the aggregation based on the context. Here's how you can modify your DAX to make it work:

Revised Measure

Measure_Aggregation =
VAR RowID = SELECTEDVALUE(table[parte1])
RETURN
    SWITCH(
        TRUE(),
        RowID = "001", SUM(table[valor]), -- Return value for row 001
        RowID = "002", SUM(table[valor]), -- Return value for row 002
        RowID = "003", 
            CALCULATE(
                SUM(table[valor]),
                FILTER(
                    ALL(table),
                    table[parte1] IN { "001", "002" } &&
                    ALLSELECTED(table[Classificação]) -- Keeps the current filter context
                )
            ), -- Sum for row 003
        BLANK() -- Default for other rows
    )

Key Changes:

1. ALLSELECTED for Context Preservation:

   • This ensures that the calculation respects the current filter context (e.g., year, quarter, month, day).
   • It prevents the measure from breaking when drilling down to other levels of the hierarchy.

2. Dynamic Filtering:

   • The FILTER function dynamically calculates only for rows 001 and 002 while maintaining the current drill-through or hierarchy context.

Explanation:

• Line 003 Calculation: For row "003", the measure dynamically sums up values for rows "001" and "002" based on the current hierarchy level. This ensures the aggregation works whether you're at the yearly, quarterly, monthly, or daily level.
• Default Row Handling: Other rows return BLANK() unless explicitly defined.

Testing:

• Verify the measure by testing in the matrix for different drill-down levels.
• Ensure your data model supports the hierarchy you are working with.

Let me know if further clarification or adjustments are needed!

[Bibiano_Geraldo · Accepted solution]

Hi @tahechadv_2022 , The problem is with ALL function that it removing all filters, here's the updated measure:

Measure_Aggregation =
VAR RowID = SELECTEDVALUE( table[parte1] )
VAR IsAggregatedRow = RowID IN { "003", "004", "005" } -- Add more rows as necessary
RETURN
    SWITCH(
        TRUE(),
        RowID = "001", SUM( table[valor] ),
        RowID = "002", SUM( table[valor] ),
        IsAggregatedRow, 
            -- Sum "001" and "002" only, maintaining the current context
            CALCULATE(
                SUM( table[valor] ),
                FILTER(
                    ALL( table ),
                    table[parte1] IN { "001", "002" }
                )
            ),
        BLANK()
    )

When you drill down, for example, into months or days, the filter context will include the specific period (e.g., January, Q1, or a day in 2024). The ALL(table) function removes any conflicting row-level filters, allowing you to sum rows "001" and "002" across the entire context (even when broken down by months, quarters, or days).

[Bibiano_Geraldo · Accepted solution]

Hi @tahechadv_2022 ,

You can achieve your goal by this DAX measure:

Measure_Aggregation =
VAR RowID = SELECTEDVALUE( table[parte1] )
RETURN
    SWITCH(
        TRUE(),
        RowID = "001", SUM( table[valor] ), -- Return value for row 001
        RowID = "002", SUM( table[valor] ), -- Return value for row 002
        RowID = "003", 
            CALCULATE( 
                SUM( table[valor] ), 
                FILTER( ALL( table ), table[parte1] IN { "001", "002" } ) 
            ), -- Sum for row 003
        BLANK() -- Default for other rows
    )

[tahechadv_2022]

This measure works well for yearly data, but when I drill down to quarter, month... even days it does not work.

Do you know how to solve this?

[Bibiano_Geraldo · Accepted solution]

Hi @tahechadv_2022 , The problem is with ALL function that it removing all filters, here's the updated measure:

Measure_Aggregation =
VAR RowID = SELECTEDVALUE( table[parte1] )
VAR IsAggregatedRow = RowID IN { "003", "004", "005" } -- Add more rows as necessary
RETURN
    SWITCH(
        TRUE(),
        RowID = "001", SUM( table[valor] ),
        RowID = "002", SUM( table[valor] ),
        IsAggregatedRow, 
            -- Sum "001" and "002" only, maintaining the current context
            CALCULATE(
                SUM( table[valor] ),
                FILTER(
                    ALL( table ),
                    table[parte1] IN { "001", "002" }
                )
            ),
        BLANK()
    )

When you drill down, for example, into months or days, the filter context will include the specific period (e.g., January, Q1, or a day in 2024). The ALL(table) function removes any conflicting row-level filters, allowing you to sum rows "001" and "002" across the entire context (even when broken down by months, quarters, or days).

[SacheeTh · Accepted solution]

Hi @Bibiano_Geraldo, 

To solve the problem of aggregating rows in a Power BI matrix where line "003" needs to display the sum or difference of rows "001" and "002," you can create a DAX measure that dynamically calculates values based on the row's identifier. The logic involves using the SWITCH function to determine how each row is calculated and leveraging the CALCULATE function with filtering for aggregation.

Here’s how you can construct the measure:

Measure_Aggregation =
VAR RowID = SELECTEDVALUE(table[parte1]) -- Identify the current row
VAR IsAggregatedRow = RowID IN { "003", "004", "005" } -- Define rows that require aggregation
RETURN
    SWITCH(
        TRUE(),
        RowID = "001", SUM(table[valor]), -- Regular summation for row 001
        RowID = "002", SUM(table[valor]), -- Regular summation for row 002
        IsAggregatedRow,
            -- Perform aggregation for specific rows (like 003)
            CALCULATE(
                SUM(table[valor]),
                FILTER(
                    ALL(table), -- Remove filters on rows
                    table[parte1] IN { "001", "002" } -- Specify rows to aggregate
                )
            ),
        BLANK() -- Return blank for rows not covered in logic
    )

This measure dynamically evaluates the parte1 column to check the row type. For rows like "001" or "002," it simply sums the valor column as usual. For rows like "003," which represent aggregated values, the measure uses CALCULATE combined with a FILTER to sum only the values from rows "001" and "002" in the current context.

The ALL function ensures that any existing filters on parte1 do not interfere with the aggregation logic, allowing the measure to sum across the specified rows regardless of the current filter context (e.g., by month, day, or year). If you drill down into periods like months or quarters, the measure respects the time-based filter while aggregating the specified rows.

By defining the aggregation logic this way, you can extend the functionality for other rows like "004" or "005" simply by adding them to the IsAggregatedRow condition and specifying the rows to include in the aggregation within the FILTER function. This approach ensures scalability and clarity in your matrix design.

[SacheeTh · Accepted solution]

Hi @tahechadv_2022,

You may try @Bibiano_Geraldo 's same query as followin, I revised a bit

To ensure that your measure works for aggregations (like rows 001, 002, and 003) across all levels of your hierarchy (e.g., year, quarter, month, day), you'll need a more dynamic DAX measure that calculates the aggregation based on the context. Here's how you can modify your DAX to make it work:

Revised Measure

Measure_Aggregation =
VAR RowID = SELECTEDVALUE(table[parte1])
RETURN
    SWITCH(
        TRUE(),
        RowID = "001", SUM(table[valor]), -- Return value for row 001
        RowID = "002", SUM(table[valor]), -- Return value for row 002
        RowID = "003", 
            CALCULATE(
                SUM(table[valor]),
                FILTER(
                    ALL(table),
                    table[parte1] IN { "001", "002" } &&
                    ALLSELECTED(table[Classificação]) -- Keeps the current filter context
                )
            ), -- Sum for row 003
        BLANK() -- Default for other rows
    )

Key Changes:

1. ALLSELECTED for Context Preservation:

   • This ensures that the calculation respects the current filter context (e.g., year, quarter, month, day).
   • It prevents the measure from breaking when drilling down to other levels of the hierarchy.

2. Dynamic Filtering:

   • The FILTER function dynamically calculates only for rows 001 and 002 while maintaining the current drill-through or hierarchy context.

Explanation:

• Line 003 Calculation: For row "003", the measure dynamically sums up values for rows "001" and "002" based on the current hierarchy level. This ensures the aggregation works whether you're at the yearly, quarterly, monthly, or daily level.
• Default Row Handling: Other rows return BLANK() unless explicitly defined.

Testing:

• Verify the measure by testing in the matrix for different drill-down levels.
• Ensure your data model supports the hierarchy you are working with.

Let me know if further clarification or adjustments are needed!