Matrix sub column addition

[Anonymous]

Hi,

This is how my Matrix looks like:

I would like to know if it is possible to add the sub columns individually as in for eg I need to have CEF total seperately and IMT/Resum total seperately in the same matrix.

For eg for the checker ID 7567, The CEF total would be 15(12+3) and IMT total for the same would be 9(4+5)

Is this possible?

eg output as in how it needs to look like for reference: 

Any help would be appreciated!

Thanks!

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
var _summarize=
SUMMARIZE(
    'Table',
    'Table'[Check ID],'Table'[Month],'Table'[Group],
    "amount",SUMX(FILTER(ALL('Table'),'Table'[Group]="CEF"&&'Table'[Check ID]=EARLIER('Table'[Check ID])),[amount]),
   "Name","Total_CEF"
    )
    return
    UNION('Table',_summarize)

2. Create a calculated table.

Table 3 =
var _summarize=
SUMMARIZE(
    'Table 2',
    'Table 2'[Check ID],'Table 2'[Month],'Table 2'[Group],
    "amount",SUMX(FILTER(ALL('Table'),'Table'[Group]=EARLIER('Table 2'[Group])&&'Table'[Check ID]=EARLIER('Table 2'[Check ID])),[amount]),
    "Name","Total_IMT"
    )
    return
    UNION('Table 2',_summarize)

3. Set Amount to Min.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@Anonymous , Parry2k has discussed a few such items on his channel. Please check video around calculation groups

https://www.youtube.com/perytus

[Anonymous]

Is it possible to achieve the same using power query?