Summarize creating a new category in the same table

Anonymous — Tue, 13 Oct 2020 10:52:51 GMT

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

Re: Summarize creating a new category in the same table

Anonymous — Wed, 14 Oct 2020 11:20:13 GMT

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

topic Re: Summarize creating a new category in the same table in DAX Commands and Tips

Summarize creating a new category in the same table

Re: Summarize creating a new category in the same table