04.12 Refer to the current row ($thisrow)

Compare each row with the group it belongs to: a state's share of its sales, every status next to its state's total, the orders in the same month. In a formula, $thisrow.column means this row's own value.

A total in a formula, such as sumif(…), normally adds up rows that match a fixed condition: sumif(status == "refunded", amount). With $thisrow, the condition can point at the row being calculated:

sumif(state == $thisrow.state, sales)

reads “the sales of every row whose state is the same as this row’s state”: this row’s state total, on every row. It’s the same as Excel’s SUMIF($A:$A, A2, $C:$C).

Where you can use it

• In a UI formula in a report or widget (04.7), inside sumif, countif or countifunique.
• After $thisrow. comes the name of a column the report groups by, i.e. one in Rows. If it isn’t there, the cell shows #ERROR, and hovering over it says “this row has no state. Add it as a grouping column.”
• The total covers all the report’s rows, after filters, not just the rows in this row’s group. Grouping the report differently doesn’t change it.
• In an SQL formula and a dataset’s Custom formula too, written as column = $thisrow.column at the start of the condition, e.g. sumif(Document_No = $thisrow.Document_No and Entry_Type = "Consumption", Quantity). See Row by row, in SQL below.

Row by row, in SQL

The Excel helper column “count this row’s output only if its document consumed material”:

if(sumif(Document_No = $thisrow.Document_No and Entry_Type = "Consumption"
         and Inventory_Posting_Group in ("RM", "BYPROD", "SCRAP", "SCRAPS"), Quantity) > 0
   and Entry_Type = "Output" and Inventory_Posting_Group = "WIP" and Quantity > 0,
   Quantity / 1000, 0)

The sumif is this row’s document’s consumption, so the row’s own Entry_Type and Quantity can sit beside it: it is a row formula, one value per row.

• In a dataset, tick Document_No, give this column Aggregate SUM, and leave Entry_Type, Inventory_Posting_Group and Quantity unticked: the formula reads them without them becoming groups. Each document gets its output. The database adds it up in one pass, as fast as any summary.
• Without Document_No = $thisrow.Document_No, the sumif has no single value per row, and the formula is refused with that explanation. Adding Entry_Type to the grouping instead would split each document into one group per entry type, and the answer would be 0 everywhere.
• Summed, the $thisrow columns must be the ones the dataset groups by.

The whole example, with sample data, the figures to check against and every message explained: case study: count a row only when its group qualifies.

Worked example: each status’s share of its state

A report grouped by state, then order status, with sales summed. Add these UI formulas (Data type: Number) and put them in Values:

On five orders (Texas: 600 delivered, 300 shipped, 100 cancelled; Ohio: 300 delivered, 100 shipped) the report reads:

• state_sales is the same on every row of a state: that state’s whole total.
• share_of_state divides the row’s own total by it. Give it the percent format (Format › Type) (04.3).
• and adds a condition of your own on top, so delivered_in_state is the delivered sales of this row’s state.

Things to know

• A total and a plain column don’t mix. sales / sumif(…) is refused; write sum(sales) / sumif(…). In a grouped report, sum(sales) is this row’s own total.
• Text goes in straight quotes in a condition, double or single: status == "delivered" or status == 'delivered'. Curly quotes (‘ ’, “ ”) are not accepted.
• Several columns at once: sumif(state == $thisrow.state and month == $thisrow.month, sales) is the total for this row’s state and month. Both must be in Rows.
• Not available yet: referring to a named row, such as the row tagged A1 (04.9), in a formula (A1 − B1). $thisrow always means the row being calculated.

Next: 04.13 Function reference

Last updated 15 October 2026