Forum Discussion

tahechadv_2022's avatar
tahechadv_2022
Icon for Helper II rankHelper II
1 year ago
Solved

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

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.

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

5 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      tahechadv_2022
      Icon for Helper II rankHelper II
      1 year ago

      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's avatar
        Bibiano_Geraldo
        Icon for Super User rankSuper User
        1 year ago

        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).