Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.

Reply
Anonymous
Not applicable

Summarize creating a new category in the same table

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)PropertyAccount typeValue
2019AAll Revenues100
2019BAll Revenues90
2019CAll Revenues80
2019A

All Costs

75
2019BAll Costs70
2019CAll Costs60
............

 

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)PropertyAccount typeValue
2019AAll Revenues100
2019BAll Revenues90
2019CAll Revenues80
2019A

All Costs

75
2019BAll Costs70
2019CAll Costs60
2019AEBITDA25
2019BEBITDA20
2019CEBITDA20

 

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

1 REPLY 1
Anonymous
Not applicable

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

Helpful resources

Announcements
FabCon Global Hackathon Carousel

FabCon Global Hackathon

Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.