Create advanced Works with: Any AI

Excel Data Model and DAX Measure Design for a Sales Report

Table output Has example

• 📊 Excel

Plan tables, relationships and measures before building the PivotTable.

The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.

[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.

Help me design an Excel data model for reporting. Business questions: [QUESTIONS]. Tables I have, with columns and a row count: [TABLES]. How the tables relate (keys): [KEYS]. Time period and calendar needs: [CALENDAR_NEEDS].

Propose which tables are facts and which are dimensions, the relationships to create (one-to-many, with the key columns), a calendar table with the columns I need, and a list of measures. For each measure give the name, a plain-language definition, a DAX formula and a worked example with invented numbers: Total Sales (3 orders of 120, 80 and 100 give 300), Sales Last Year, Year-on-Year change as a percentage, a running total, and a distinct customer count. Mark every formula [VERIFY] and say what to check. Explain filter context with an example: the same Total Sales measure shows different numbers in different PivotTable cells because each cell filters the data differently.

Warn about relationships in the wrong direction, duplicated keys on the one side, text keys with trailing spaces, measures versus calculated columns, and blank rows from missing keys. Add a short glossary (fact table, dimension, relationship, measure, filter context), a build order (load tables, relationships, calendar, measures, PivotTable, test), and five checks on the finished model: totals match the source, no blank category, continuous dates, same result at different levels, and refresh without errors. Suggest loading only needed columns, documenting measures in a sheet, and a list of questions the model cannot answer. Check the Excel version needed for the data model and Power Pivot on my installation [VERIFY].

Fill in before use

Optional: type your details and the prompt above updates. Nothing is saved or sent.

Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.

Invented measure rows. Total Sales = SUM(Sales[Amount]); example: 3 orders of 120, 80 and 100 give 300. Sales Last Year = same period in the previous year; example 250. Year-on-year change % = (300 - 250) / 250 = 20 percent. Check: all values to be tested in your model [VERIFY]. Running total example: monthly sales of 100, 120 and 80 give running totals of 100, 220 and 300. Check on the model: the grand total of Total Sales in a PivotTable must equal the sum of the Amount column in the source table. Share of total example: 120 of 300 is 40 percent. Test each measure with a table visual before using it in a chart.

• excel