8. Adding Columns: Power Query Add Column Tab and DAX Calculated Columns
8.13 Implicit vs Explicit Measures, Measure Tables, Display Folders and Formatting
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
- Home › Enter data › name the table
_Measures› leave the single column › Load. - 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. - 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
- Open Model view. Select one or more measures (Ctrl + click).
- In Properties › Display folder, type
Sales(or a sub-folder with a backslash:Delivery\Speed). - Suggested folders for our model: Sales, Orders, Delivery, Customers, Time Intelligence, Platform Comparison.
Steps in Power BI – formatting measures
- Select the measure in the Data pane.
- 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.
- 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).
- 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.
Ravindra Bagale's Tip – मराठी
मित्रांनो, लक्षात ठेवा: फक्त ₹ दाखवण्यासाठी measure मध्ये FORMAT() वापरलं तर measure text होतो, जो chart axis वर plot होत नाही आणि चुकीचा sort होतो. Measures numeric ठेवा आणि Format settings किंवा dynamic format strings वापरा. तसंच ID आणि year columns वर Summarization › Don't summarize set करा म्हणजे त्यांची चुकून बेरीज होणार नाही. हे exam आणि interview दोन्हीसाठी important आहे.
Ravindra Bagale's Tip – हिंदी
दोस्तों, याद रखो: सिर्फ़ ₹ दिखाने के लिए measure में FORMAT() इस्तेमाल करने से measure text बन जाता है, जो chart axis पर plot नहीं होता और गलत sort होता है. Measures numeric रखो और Format settings या dynamic format strings इस्तेमाल करो. साथ ही ID और year columns पर Summarization › Don't summarize set करो ताकि उनका गलती से जोड़ न हो. यह exam और interview दोनों के लिए ज़रूरी है.
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.