Ravindra BagaleCourses & study guides मराठी Track your progress

8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns

8.13 Implicit vs Explicit Measures, Measure Tables, Display Folders and Formatting

By Last updated:

In short: An implicit measure is created when you drag a column (Sum of Amount). An explicit measure is written in DAX and named.

Start with a problem

Salman drags Amount into a card and gets ₹2,100; he switches it to Average and gets ₹262.5, which the business says is wrong (AOV is ₹300).

Explain simply

An implicit measure is created when you drag a column (Sum of Amount). An explicit measure is written in DAX and named. Keep explicit measures in a measure table, grouped in display folders, with proper formats.

Show the data

KPI Value Hand calculation
Total Sales ₹2,100 400+200+300+150+450+250+350+0
Orders 8 301 to 308
Delivered orders 7 all except 308
AOV ₹300 2,100 ÷ 7
% under 10 min 42.9% 3 ÷ 7 (301, 304, 305)
Cancellation rate 12.5% 1 ÷ 8

About this data: This table uses columns: KPI, Value, Hand calculation. This table uses columns: Total Sales, Delivered Orders, AOV. This table uses columns: Measure, Implicit result, Explicit result. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

  1. Drag Amount to a card › change to Average.

    Your result should look like this:

    Card
    ₹262.5 (2,100 ÷ 8, includes cancelled 308)

  2. Home › Enter data › table _Measures › Load.

  3. New measures in _Measures: Total Sales = SUM(Orders[Amount]), Delivered Orders = CALCULATE(COUNTROWS(Orders), Orders[Status] = "Delivered"), AOV = DIVIDE([Total Sales], [Delivered Orders]).

    Your result should look like this:

    Total Sales Delivered Orders AOV
    ₹2,100 7 ₹300

  4. Model view › select the three › Properties › Display folder Sales. Format Total Sales and AOV as Currency ₹ English (India), 0 decimals.

Show the result

Your result should look like this (fictional sample):

Measure Implicit result Explicit result
Sales Sum of Amount ₹2,100 Total Sales ₹2,100
Average order Average of Amount ₹262.5 ✗ AOV ₹300 ✓

Explain what happened

The implicit average divided by all 8 rows; the explicit AOV uses the business definition (delivered orders only).

Explore further

  • Calculation groups (21.7) require explicit measures.

Detailed reference for this lesson:

Implicit measure: drag Amount into a visual and Power BI creates Sum of Amount automatically (the Σ icon in the Data pane shows columns that summarise by default). Explicit measure: a measure you write, such as Total Sales = SUM(Orders[Amount]). Professionals use explicit measures because they are reusable, named clearly and formatted once. They are also needed for some features (for example calculation groups discourage implicit measures). Set Column tools › Summarization › Don't summarize on ID and year columns so they are never summed by mistake.

Steps in Power BI – create a measure table

  1. Home › Enter data › name the table _Measures › leave the single column › Load.
  2. Create measures with the table selected (Home › New measure), or move existing ones: select a measure in the Data pane › Measure tools › Home table › _Measures.
  3. Delete the dummy Column1. The table icon changes to a calculator icon, and the table sorts to the top of the Data pane.

Steps in Power BI – display folders

  1. Open Model view. Select one or more measures (Ctrl + click).
  2. In Properties › Display folder, type Sales (or a sub-folder with a backslash: Delivery\Speed).
  3. Suggested folders for our model: Sales, Orders, Delivery, Customers, Time Intelligence, Platform Comparison.

Steps in Power BI – formatting measures

  1. Select the measure in the Data pane.
  2. Measure tools › Format: Currency (choose ₹ English (India) from the currency drop-down), Percentage, Whole number, Decimal number; set decimal places with the arrows; toggle the thousands separator.
  3. For special labels use dynamic format strings: Measure tools › Format › Dynamic, then write a DAX expression that returns a format string (the measure itself stays numeric).
  4. Add a Description in Model view › Properties. It appears as a tooltip in the Data pane.
-- Dynamic format string for [Total Sales]: a comma before the decimal point scales by 1,000
SWITCH(TRUE(),
    [Total Sales] >= 1000000, "₹#,0,,.0M",
    [Total Sales] >= 1000,    "₹#,0,.0K",
    "₹#,0")

Lakh and crore labels

Format strings scale fakt in thousands (K, M). If the trainer wants "₹12.5 L" or "₹1.2 Cr" on a card, create a separate label measure such as FORMAT([Total Sales] / 100000, "₹0.0") & " L". Use it only for display (text). Keep the numeric measure for charts. Also look at the visual's Display units option in the Format pane.

Ravindra Bagale's Tip

Friends, remember: using FORMAT() inside a measure just to show ₹ turns the measure into text, which can't be plotted on a chart axis and sorts wrongly. Keep measures numeric and use the Format settings or dynamic format strings. Also set Summarization › Don't summarize on ID and year columns so they are never summed by mistake. This matters for both exams and interviews.

Practice task

Create a _Measures table, move Total Sales, Total Orders, AOV, Avg Delivery Time (mins) and Cancellation Rate into it, organise them into display folders, and format them (₹ with 0 decimals, % with 1 decimal, minutes with 1 decimal).

Fix common mistakes

Wrong result Reason Correction
AOV ₹262.5 Implicit average includes cancelled Explicit AOV measure
Measures scattered in tables No measure table Create _Measures and set Home table
Numbers show 2100.000000 No format Set Currency format

Practise independently

Practice task

What is AOV if order 308 were also delivered at ₹0?

Hints
  • 2,100 ÷ 8.
Solution

₹262.5 – which shows why the definition of delivered matters.

Think like a professional

Explicit, documented measures are a hallmark of a professional model.

Loading DAX practice tasks...

  1. Total fees (Easy): Measure: Total Fee = add up the Fee column of Visits.
  2. Visit count (Easy): Measure: Visit Count = number of rows in Visits.
  3. Average wait (Easy): Measure: Avg Wait = average of WaitMins.
  4. Different patients (Easy): Measure: Patients = number of different patients.
  5. Fee per visit by department (Medium): Shown per Department. Measure: Fee per Visit = [Total Fees] ÷ number of visits.
  6. Longest wait per city (Medium): Shown per Doctors City. Measure: Max Wait = largest WaitMins.
  7. Insured fees (Medium): Measure: Insured Fees = [Total Fees] where Insured is "Yes".
  8. Insured share by department (Hard): Shown per Department. Measure: Insured Share = insured fees ÷ [Total Fees].
  9. Emergencies per doctor (Hard): Shown per DoctorName. Measure: Emergencies = number of visits with VisitType "Emergency".
  10. Long waits by visit type (Hard): Shown per VisitType. Measure: Long Waits = number of visits with WaitMins more than 20.

See all Power BI practice tasks

Ravi Sir's Cards for this lesson

Implicit vs explicit measures and measure tables. Implicit totals are quick but hard to reuse and control. Measures scattered in tables are hard to find.Revision cardWhat is an implicit measure, and why prefer explicit measures? An implicit measure is created when you drag a number field into a visual and Power BI sums it. An explicit measure is written in DAX. ExInterview cardImplicit vs explicit measure. Both are measures, formulas that give one number for the current filters. An implicit one is made by dragging a field. An explicit one is written in DAX.Comparison card

मराठी cards