NETWORK.ROLLUP OpenBI

Multiplies along every path from the starts and adds up the paths: the parts a forecast needs, costs rolled up into products, what an owner really holds when companies own companies, support costs shared out.

Each path from a start to a node contributes the start's amount times the weights along that path, multiplied; a node's Total is all those contributions added. A start's own amount is part of its own total. Where links loop back there are endless paths; with loops "solve" their sum is worked out exactly, as Leontief's input-output model does.

Group: Networks. OpenBI’s own: DAX has no function of this name.

Syntax

NETWORK.ROLLUP ( <links>, <from>, <to>, <weight>, <starts>, <start> [, <amount>] [, <direction>] [, <loops>] [, <by start>] )

Returns

Table. A row for each node the starts reach, with the columns Node, Level and Total; by start, Start comes first and there is a row for each start and each node it reaches.

Remarks

• Level is the most links from a start to the node, the low-level code planning systems use: a part used both in a product and inside one of its parts gets the deeper level. Inside a loop it is blank, and a loop counts as one step for what lies beyond it.
• Several rows between the same two nodes, in the same direction, add up, worked out exactly: +0.3, -0.3 and 0.1 give 0.1, not a number a hair away. Rows that add up to exactly 0 are no link at all.
• A row whose two ends are the same node is a loop of one: a company holding its own shares.
• A solved loop needs its paths to shrink as they go round: a single ring converges when its weights, taken without their signs, multiply to less than 1. Rings that share nodes count together: a node with a ring of 0.6 on itself and another of 0.6 through a neighbour has no finite total, though either ring alone would. A loop that doesn't converge (shares of 100 % held both ways) is an error that names it.
• With cross-holdings the paths go round: if Ola holds 40 % of Nord AS, and Nord AS and Sør AS hold 30 % and 20 % of each other, Ola's share of Nord AS comes to 0.4 / (1 - 0.3 x 0.2) = 42.6 %, and 30 % of that in Sør AS. A company's total in itself comes out above 1: what it holds of itself through the loop.
• A start that no link has still gets its own row: its amount, at level 0. The starts are nodes too, numbered by value with the links' ends.
• The links are read as the filters leave them, so a bill of materials as of a date is the table filtered to the rows valid then: NETWORK.ROLLUP ( FILTER ( Bom, Bom[Valid from] <= d && Bom[Valid to] >= d ), ... ). In a measure, the slicers do it.
• The call is refused before the work starts when its answer would have more than 2,000,000 rows (by start), or when its work is reckoned at more than about 20 seconds; the error says which, and how long. Keep only the starts and links that matter, or leave by start out.
• The rows come by start (with by start), then by level, then in the nodes' order: numbers from the smallest, then text by character code with capitals read as small letters, then dates, then FALSE and TRUE. The nodes of a loop, their level blank, come where the loop sits: after the level before it, before the level after it. Rows that come in another order give the same answer.
• Nodes are compared the way OAX compares values: text without regard to case, and 1 and 1.0 are the same node. A node written two ways is shown with the spelling whose characters sort first by code (ALICE before Alice); a column of the model keeps one spelling of each text, the first it loaded.

Compared with DAX

An OpenBI function: DAX has none like it, so a measure or query that uses it works in OpenBI only, not in Power BI or Analysis Services.

DAX has no recursion: a total over paths of any length has to be written out level by level, to a depth fixed in advance, and nothing in it solves a loop. NETWORK.ROLLUP does both in one call. Its answers are checked against an independent reference (exact fractions, every path counted, a dense solve), and it reproduces Statistics Norway's and Statistics Finland's published Leontief inverses from their published flows.

Examples

Spokes for 500 bikes

Two wheels a bike, 36 spokes a wheel: 500 x 2 x 36 = 36,000 spokes. Level says how deep each part sits.

EVALUATE
VAR Bom =
    DATATABLE (
        "Parent", STRING, "Part", STRING, "Quantity", INTEGER,
        {
            { "Bike", "Wheel", 2 }, { "Bike", "Frame", 1 }, { "Wheel", "Spoke", 36 }, { "Wheel", "Tyre", 1 }
        }
    )
VAR Forecast = DATATABLE ( "Product", STRING, "Units", INTEGER, { { "Bike", 500 } } )
RETURN
    NETWORK.ROLLUP ( Bom, [Parent], [Part], [Quantity], Forecast, [Product], [Units] )

Who ultimately owns Drift AS

Against the holdings from Drift AS: Ola holds 60 % of Holding AS, which holds 50 % of Drift AS, so Ola's share is 30 %; Kari's 50 % is direct.

EVALUATE
VAR Shares =
    DATATABLE (
        "Owner", STRING, "Company", STRING, "Share", DOUBLE,
        {
            { "Ola", "Holding AS", 0.6 }, { "Holding AS", "Drift AS", 0.5 }, { "Kari", "Drift AS", 0.5 }
        }
    )
VAR Target = DATATABLE ( "Company", STRING, { { "Drift AS" } } )
RETURN
    NETWORK.ROLLUP ( Shares, [Owner], [Company], [Share], Target, [Company], , "in" )

Support costs allocated back and forth

The reciprocal method, on the textbook example of Hansen, Mowen and Guan (Cost Management: Accounting and Control): Maintenance (160,000) and Power (250,000) serve each other and the two producing departments. Solved, Maintenance comes to 214,286 and Power to 271,429 with what they charge each other, and Grinding and Assembly get all 410,000 between them.

EVALUATE
VAR Services =
    DATATABLE (
        "From", STRING, "To", STRING, "Share", DOUBLE,
        {
            { "Power", "Maintenance", 0.2 }, { "Power", "Grinding", 0.6 }, { "Power", "Assembly", 0.2 },
            { "Maintenance", "Power", 0.1 }, { "Maintenance", "Grinding", 0.45 }, { "Maintenance", "Assembly", 0.45 }
        }
    )
VAR Costs = DATATABLE ( "Department", STRING, "Cost", INTEGER, { { "Maintenance", 160000 }, { "Power", 250000 } } )
RETURN
    SELECTCOLUMNS (
        NETWORK.ROLLUP ( Services, [From], [To], [Share], Costs, [Department], [Cost], , "solve" ),
        "Department", [Node],
        "Level", [Level],
        "Total", ROUND ( [Total], 2 )
    )

Related functions

NETWORK.REACH, NETWORK.PATH, NETWORK.CYCLES, PATH.

Open Graph, OpenBI's graph library, does the work: rollup. See the OAX network functions and Open Graph.

Last updated 6 November 2026 · OpenBI Studio 0.3.78