Ravindra BagaleCourses & study guides

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:

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

Mitrano, lakshat theva: 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. He exam aani interview doghansathi important aahe.

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).

Thodkyaat sangaycha tar (quick recap)

Static row-level columns Power Query madhe, filter nusar badalnare hishob measures madhe. Ratio kadhihi column madhe average karu naka. Measures numeric theva aani format settings vapra. Aata pudhe jaauya – folder madhun anek files ekatra karuya.