QlikToDax

Avg(Aggr(Sum(Sales), Customer))

Aggr (nested aggregation) in Power BI

Aggr builds a virtual table of aggregated values, for example sales per customer, so you can aggregate again. In DAX that is an iterator (AVERAGEX, MAXX, COUNTROWS(FILTER(...))) over VALUES or SUMMARIZE that calls a measure.

Partial / different approach DAX

Short answer: Avg(Aggr(Sum(Sales), Customer)) becomes AVERAGEX(VALUES(Customer[CustomerKey]), [Sales]). Always call a measure inside the iterator. Aggr used as a calculated dimension has no direct equivalent.

What it does in Qlik

Aggr(expr, dim1, dim2...) evaluates expr once for each combination of the listed dimensions that exists in the current selection. It returns that array to an outer aggregation such as Avg, Max or Count. As a chart dimension it creates a calculated dimension, for example buckets of customers by sales.

Qlik example

Qlik chart expressions
Avg(Aggr(Sum(Sales), Customer))                       // average sales per customer
Max(Aggr(Sum(Sales), Customer, Year))                 // best customer-year
Count(Aggr(If(Sum(Sales) > 1000, Customer), Customer)) // customers above 1000

Power BI equivalent

Average / max of a per-customer value

DAX
Avg sales per customer =
AVERAGEX ( VALUES ( Customer[CustomerKey] ), [Sales] )

Best customer-year =
MAXX (
    SUMMARIZE ( Sales, Customer[CustomerKey], 'Date'[Year] ),
    [Sales]
)

Count members meeting a condition

DAX
Customers above 1000 =
COUNTROWS ( FILTER ( VALUES ( Customer[CustomerKey] ), [Sales] > 1000 ) )

Aggr as a calculated dimension

A Power BI visual axis must be a model column. Use a calculated column if the bucket can be fixed at refresh time, for example Customer[Sales band]. If the bucket must react to slicers, use the "dynamic segmentation" pattern: a disconnected bands table plus a measure that counts customers per band.

Gotchas and behaviour differences

  • Context transition. AVERAGEX(VALUES(Customer[CustomerKey]), SUM(Sales[Amount])) returns the grand total on every row, because a bare SUM is not filtered by the iterated customer. A measure reference ([Sales]) or CALCULATE(SUM(...)) triggers context transition.
  • VALUES(Customer[CustomerKey]) includes customers with no sales in the current filter. Their [Sales] is blank and AVERAGEX skips blanks, so the average matches Qlik. COUNTROWS of the plain list would not.
  • Aggr with NODISTINCT, or Aggr whose dimensions differ from the chart's, is often used for "value of the parent level" logic. In DAX that is usually CALCULATE with ALLSELECTED or REMOVEFILTERS on the child columns, not an iterator.
  • SUMMARIZE over the fact table returns only combinations that exist in the data. That is the same as Aggr's behaviour.

Official documentation

Related pages

Sum(TOTAL Sales) / Sum(TOTAL <Category> Sales) The TOTAL qualifier Qlik's TOTAL qualifier makes an aggregation ignore the chart dimensions but keep the selections. In DAX that is ALLSELECTED. TOTAL <Field> keeps one dimension, which maps to removing only the other dimension columns. Direct equivalent {<Product={"Bike*"}>} / {<Customer={"=Sum(Sales)>10000"}>} Searches in set modifiers (wildcards, >, aggregated conditions) How Qlik searches inside set modifiers (numeric ranges, wildcards and expression searches such as "=Sum(Sales)>10000") translate to DAX filters, and why the expression search needs ALLSELECTED to match. Partial / different approach FirstSortedValue(Product, -Aggr(Sum(Sales), Product)) FirstSortedValue FirstSortedValue returns the value of one field on the record with the lowest (or, with a minus sign, highest) sort weight, such as the top-selling product or the latest status. In DAX, use TOPN plus a tie check, or LASTNONBLANKVALUE for "latest". Partial / different approach Rank(Sum(Sales)) Rank Qlik's Rank() ranks rows within the current column segment and has its own tie modes and a text format ("3 - 4"). In DAX, use RANKX or the newer RANK window function. Pick the ties option deliberately. Partial / different approach

Spotted an error or a better pattern? QlikToDax is maintained by a working BI consultant; corrections are welcome via the About page. Last reviewed 2026-10-25.