Summarize creating a new category in the same table

[Anonymous]

Hi,

I have create the following table summarizing a data table as follows:

this_table =

SUMMARIZE(

'main_table',

ROLLUP(

main_table[fiscal year (type)]),

main_table[Poperty],

main_table[Account type],

"Value",

SUM(main_table[Value]))

resulting in something like this:

Fiscal Year (type)	Property	Account type	Value
2019	A	All Revenues	100
2019	B	All Revenues	90
2019	C	All Revenues	80
2019	A	All Costs	75
2019	B	All Costs	70
2019	C	All Costs	60
...	...	...	...

Now, I need to create another category of "Account type", which will be the EBITDA (All Revenues - All costs), resulting in something like:

Fiscal Year (type)	Property	Account type	Value
2019	A	All Revenues	100
2019	B	All Revenues	90
2019	C	All Revenues	80
2019	A	All Costs	75
2019	B	All Costs	70
2019	C	All Costs	60
2019	A	EBITDA	25
2019	B	EBITDA	20
2019	C	EBITDA	20

What's the easiest way to do it?

The table was created as a table, not as a new request, so it's not available in the transformation menu.

Thank you

[Anonymous]

this_table =
var __coreTable =
    SUMMARIZE(
        'main_table',
        ROLLUP( main_table[fiscal year (type)] ),
        main_table[Poperty],
        main_table[Account type],
        "@Value", SUM( main_table[Value] )
    )
var __additionalAccountType =
    ADDCOLUMNS(
        CROSSJOIN(
            DISTINCT( main_table[fiscal year (type)] ),
            distinct( main_table[Property] )
        ),
        "Account type",
            "EBITDA",
        "@Value",
            var __fy = main_table[fiscal year (type)]
            var __pr = main_table[Property]
            var __rev =
                MAXX(
                    filter(
                        __coreTable,
                        main_table[fiscal year (type)] = __fy
                        &&
                        main_table[Property] = _pr
                        &&
                        main_table[Account type] = "All Revenues"
                    ),
                    [@Value]
                )
            var __cost =
                MAXX(
                    filter(
                        __coreTable,
                        main_table[fiscal year (type)] = __fy
                        &&
                        main_table[Property] = _pr
                        &&
                        main_table[Account type] = "All Costs"
                    ),
                    [@Value]
                )
            return
                __rev - __cost
    )
var __union =
    union(
        __coreTable,
        __additionalAccountType
    )
return
    __union