Power BI running total and category rank in DAX, with no helper columns

Checked 10 November 2026 · Verification: Python 3.12.3, pandas 2.1.4 · 9-minute read

Two of the most-asked DAX questions have the same answer shape: change the filter context, then aggregate. A running total sums everything up to the current month. A rank compares one category's total against every visible category's total. Both are one measure each, both work with slicers, and neither needs a helper column. The headline result: the 2025 running total reaches $122,707.17 by October, and Desks ranks first among the three categories at $56,958.60.

What you need

• Power BI Desktop on Windows. Desktop is a Windows application; there is no macOS or Linux version, and no headless runner. The Desktop steps in this guide were not run on the machine that built it — see the verification note at the bottom.
• The sample file the sales file (CSV): columns Month, Category, Units, and Revenue. One row per category per month, 66 rows.
• Nothing else. No database, no service, no account.

1. Import the sales file

In Power BI Desktop, go to Home > Get data > Text/CSV, pick the sales file, and choose Load. Check the column types after loading: Month must be a date, Revenue a decimal number. If Month loads as text, the relationship in step 3 will not work — change the type in Power Query first.

2. Build the calendar table

The running total filters on dates, so it needs the same continuous date table as any time-based measure. Microsoft's documentation says to mark one of the tables containing a date column as the date table before using time-intelligence functions (Microsoft Learn: Set and use date tables in Power BI Desktop). Create it as a calculated table:

Modeling > New table

Calendar =
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) )

Then mark it: select the Calendar table in the Fields pane, right-click, and choose Mark as date table > Mark as date table, with Date as the date column. The range must cover every month in the sales file.

3. Relate the two tables

In Model view, drag Calendar[Date] onto Sales[Month]. The relationship is one-to-many, single direction, from Calendar to Sales. The running total in step 4 filters the calendar side, so this relationship is what lets it reach the sales rows.

4. Write the three measures

Create each measure on the Sales table (Modeling > New measure). The measures file (DAX) holds the same three measures, ready to paste.

Total Sales = SUM ( Sales[Revenue] )

Running Total Sales =
CALCULATE (
    [Total Sales],
    FILTER (
        ALLSELECTED ( Calendar[Date] ),
        Calendar[Date] <= MAX ( Calendar[Date] )
    )
)

Category Rank =
RANKX (
    ALLSELECTED ( Sales[Category] ),
    [Total Sales],
    ,
    DESC,
    SKIP
)

How the running total works: CALCULATE evaluates an expression in a modified filter context (Microsoft Learn, CALCULATE function (DAX)). Here the modification is a FILTER over ALLSELECTED ( Calendar[Date] ) — every date the slicers leave visible — keeping only dates up to the current row's date, which MAX ( Calendar[Date] ) supplies. The sum then covers January through the current month. ALLSELECTED matters: it keeps slicer filters, so filtering the report to 2025 restarts the running total at January 2025 instead of carrying 2024 with it (Microsoft Learn, ALLSELECTED function (DAX)).

How the rank works: RANKX returns the ranking of a number in a list of numbers for each row in the table argument (Microsoft Learn, RANKX function (DAX)). The table here is ALLSELECTED ( Sales[Category] ) — the visible categories — and the expression is [Total Sales], evaluated once per category. DESC puts the largest total at rank 1; SKIP means tied categories share a rank and the next rank is skipped (1, 1, 3), the usual leaderboard behaviour.

5. Put them in visuals

For the running total, add a line chart with Calendar[Date] (month level) on the x-axis and [Running Total Sales] as the value. Add [Total Sales] as a second series if you want the monthly bars underneath the line, as in the chart above. For the rank, add a table visual with Sales[Category], [Total Sales], and [Category Rank], sorted by rank ascending. Add a year slicer on Calendar[Date] and set it to 2025: the running total restarts at January, and the rank recomputes over the filtered year.

6. Check the numbers

Put Calendar[Date] (month level), [Total Sales], and [Running Total Sales] in a table visual, filtered to 2025. Every row must match this table, which was computed independently from the same CSV:

And the rank table, filtered to January–October 2025, must read:

If the running total equals the month's sales on every row, the FILTER is not reaching the calendar — check the relationship in step 3. If every category ranks 1, the RANKX table argument is wrong — see the first trap below.

Traps

End state

A line chart that climbs from $11,542.57 in January to $122,707.17 in October 2025, and a three-row rank table with Desks first. Both measures respect slicers: filter to a single quarter and the running total restarts at that quarter's first month while the rank recomputes over the visible categories. The same two patterns — CALCULATE with a date-bounded FILTER, and RANKX over ALLSELECTED — cover most cumulative and leaderboard reporting.

Sources

• Microsoft Learn, CALCULATE function (DAX) — evaluates an expression in a modified filter context
• Microsoft Learn, RANKX function (DAX) — returns the ranking of a number in a list of numbers for each row in the table argument
• Microsoft Learn, ALLSELECTED function (DAX) — removes filters from the visual while keeping slicer filters
• Microsoft Learn — Set and use date tables in Power BI Desktop — when a date table must be marked, and how to mark it

Verified 10 November 2026. The running totals and ranks in the check tables were computed in Python 3.12.3 with pandas 2.1.4 by cumulative-summing the same sales file by month and ranking category totals for January–October 2025 — the identical aggregations the DAX performs. Running total at October 2025: $122,707.17. Ranks: Desks 1 ($56,958.60), Chairs 2 ($41,516.59), and Lamps 3 ($24,231.98). The Power BI Desktop interface steps (import, calendar table, relationship, and visuals) were documented from Microsoft Learn and were not run on this machine: Power BI Desktop is Windows-only and is not installed here. Run the check tables against your own report; if a row disagrees, the model differs from the one described above.