Power BI category share of total in DAX: one DIVIDE measure, no helper column
“What share of sales is Desks?” is a composition question, and it needs one measure, not a helper column. The measure divides each category's sales by the sales of all categories in the same period. The headline result: for January–October 2025, Desks took 46.42% of $122,707.17 in sales, Chairs 33.83%, and Lamps 19.75%. The whole trick is the denominator — one wrong argument to ALL and the shares stop adding to 100%.
Key result: January–October 2025 sales of $122,707.17 split Desks 46.42% ($56,958.60), Chairs 33.83% ($41,516.59), and Lamps 19.75% ($24,231.98). In October 2025 alone: Desks 46.39%, Chairs 34.16%, Lamps 19.45% of $13,436.42.
Download the data and the measures: the sales file (CSV) (66 rows, MD5 51a16fd9e023dd0208564a0c2c1a089e) and the measures file (DAX) (the two measures below, ready to paste). The data is a constructed sample — three product lines, monthly revenue from January 2024 to October 2025 — not a real company's sales.
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 sample file the sales file (CSV): columns
Month,Category,Units, andRevenue. 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, and Units a whole 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 share measure has to respect the month filter, so it needs a date table: a table with one row per day, continuous, with no gaps. 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:
Calendar =
ADDCOLUMNS (
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"MonthNumber", MONTH ( [Date] ),
"MonthName", FORMAT ( [Date], "mmmm" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"MonthYear", FORMAT ( [Date], "mmm yyyy" )
)CALENDAR on its own returns a table with a single Date column. ADDCOLUMNS adds the columns the visuals need: Year and MonthNumber as numbers, MonthName as the full month name, Quarter as Q1 to Q4, and MonthYear as a label such as Jan 2025. Marking the table as the date table removes the built-in date hierarchies Desktop would otherwise generate. Build one by hand instead: in the Fields pane, right-click Year and choose Add to hierarchy > New hierarchy. Then drag Quarter and MonthName in below Year, in that order. Then sort the months in calendar order: select MonthName and choose Sort by column > MonthNumber on the Column tools ribbon. The full treatment is in Build a Power BI date table in DAX.
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 same Learn page describes both the right-click path and the Table tools ribbon path. The range must cover every month in the sales file — January 2024 through October 2025 — or the month filter in step 5 has nothing to filter on.
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. One calendar date matches many sales rows; no sales row matches more than one calendar date. If the relationship shows as many-to-many or inactive, the Month column is the wrong type — go back to step 1 and fix it. This relationship is what carries the month filter into the share measure in step 4, so a broken relationship here means every share in the report is a share of everything, not of the month shown.
4. Write the two measures
Create each measure on the Sales table (Modeling > New measure). The measures file (DAX) holds the same two measures, ready to paste.
Total Sales = SUM ( Sales[Revenue] )
Category Share % =
DIVIDE (
[Total Sales],
CALCULATE (
[Total Sales],
ALL ( Sales[Category] )
)
)How it works: the numerator is the category's sales in the current filter context — one row of the visual means one category in one month. The denominator reevaluates [Total Sales] with the category filter removed: CALCULATE evaluates an expression in a modified filter context (Microsoft Learn, CALCULATE function (DAX)), and ALL ( Sales[Category] ) removes the filters from that column only — all other filters still apply (Microsoft Learn, ALL function (DAX)). So in an October 2025 row, the denominator is all categories in October 2025, not all categories ever. DIVIDE handles the empty case: a category with no sales in the period returns blank instead of an error (Microsoft Learn, DIVIDE function (DAX)). Microsoft's own documentation builds exactly this pattern — a category's contribution to total sales per year, keeping the year filter and removing the category filter — as its third ALL example (Microsoft Learn, ALL function (DAX)).
Format [Category Share %] as a percentage with two decimals. The measure returns a decimal (0.4642), and the percentage format is what makes it read as 46.42%.
5. Put them in a visual
Add a 100% stacked bar chart. Put Sales[Category] on the legend, the Calendar hierarchy (Year > Quarter > MonthName), drilled to the month level, on the axis, and [Total Sales] as the value. Add a table visual with Sales[Category], [Total Sales], and [Category Share %] alongside it, so the percentages are readable as numbers, not just bar lengths. Filter the page to 2025 if you want the year-to-date view only; the shares still compute per month, because the denominator keeps the month filter and removes only the category filter.
6. Check the numbers
Put Sales[Category], [Total Sales], and [Category Share %] in a table visual, filtered to January–October 2025, and then filtered to October 2025 alone. Every row must match these tables, which were computed independently from the same CSV:
| Category | Total Sales ($) | Share % |
|---|---|---|
| Desks | 56,958.60 | 46.42% |
| Chairs | 41,516.59 | 33.83% |
| Lamps | 24,231.98 | 19.75% |
| Jan–Oct 2025 total | 122,707.17 | 100.00% |
| Category | Total Sales ($) | Share % |
|---|---|---|
| Desks | 6,233.12 | 46.39% |
| Chairs | 4,590.14 | 34.16% |
| Lamps | 2,613.16 | 19.45% |
| October 2025 total | 13,436.42 | 100.00% |
The first check is the total row: the shares must add to 100.00%. If they add to anything else, the denominator is wrong — see the first trap below. The second check is the month: October's shares (46.39 / 34.16 / 19.45) differ slightly from the ten-month shares (46.42 / 33.83 / 19.75), which is how you know the month filter survived. If October's shares equal the ten-month shares exactly, the month filter was removed along with the category filter.
Traps
TRAP — ALL(Sales) REMOVES THE MONTH TOO. Writing ALL ( Sales ) instead of ALL ( Sales[Category] ) removes every filter on the Sales table, including the month filter that arrives through the relationship from the calendar. The denominator becomes the all-time total of $244,788.76 across all 22 months in the file, so the January–October 2025 shares add to 50.13% instead of 100.00%, and October Desks reads 2.55% instead of 46.39%. The measure looks plausible — every row has a number — and it is wrong in every row. Microsoft documents the distinction directly: ALL (Column) removes filters from the specified columns while all other filters still apply; ALL (Table) removes all filters from the table (Microsoft Learn, ALL function (DAX)).
TRAP — A HELPER COLUMN COMPUTES THE WRONG TOTAL. A calculated column that divides each row's revenue by the table's total revenue bakes in one fixed denominator at refresh time. It cannot respond to a slicer or a visual filter, so filtering to October 2025 still shows shares of the all-time total. A measure recomputes the denominator in every filter context, which is the whole point of the pattern. If a share never changes when you filter, it is a column, not a measure.
TRAP — THE DIVIDE OPERATOR ON AN EMPTY CATEGORY. Writing [Total Sales] / CALCULATE ( [Total Sales], ALL ( Sales[Category] ) ) with the division operator errors on a category with no sales in the period. DIVIDE returns blank instead. The first month of any new product line is exactly that case.
End state
A calendar table marked as the date table, a one-to-many relationship to sales, and two measures. The table visual shows three categories splitting $122,707.17 over January–October 2025 — Desks 46.42%, Chairs 33.83%, Lamps 19.75% — and the same three categories splitting the October 2025 total of $13,436.42 at 46.39%, 34.16%, and 19.45%. The same two measures work on any sales table with a category column and a calendar table — only the table and column names change.
Sources
- Microsoft Learn, ALL function (DAX) — ALL(Column) removes filters from the specified columns while all other filters still apply; includes the category-contribution-to-total-sales example this guide follows
- Microsoft Learn, CALCULATE function (DAX) — evaluates an expression in a modified filter context
- Microsoft Learn, DIVIDE function (DAX) — performs division and returns BLANK() on division by zero
- Microsoft Learn — Set and use date tables in Power BI Desktop — when a date table must be marked, and how to mark it
Checked 11 November 2026. The measure results in the check tables were computed in Python 3.12.3 with pandas 2.1.4 by grouping the same sales file by category and month — the identical aggregation the DAX performs. January–October 2025: Desks $56,958.60 (46.42%), Chairs $41,516.59 (33.83%), Lamps $24,231.98 (19.75%), total $122,707.17. October 2025: Desks $6,233.12 (46.39%), Chairs $4,590.14 (34.16%), Lamps $2,613.16 (19.45%), total $13,436.42. Run the check tables against your own report; if a row disagrees, the model differs from the one described above.