ساخت ستون‌‌های محاسباتی در Power BI

گاهی داده‌ای که تحلیل می‌کنید، حوزه خاصی را که به دنبال نتیجه‌گیری از آن هستید در بر ندارد، اینجاست که ستون‌های محاسباتی مطرح می‌شوند. ستون‌های محاسباتی ،ستون‌ جدیدی می‌باشند که از فرمول‌های (Data Analysis Expressions (DAX، استفاده می‌کنند و با تعریف یک محاسبه برای تبدیل یا ترکیب داده‌های موجود در دو یا چند ستون ساخته می‌شوند. این آموزش به شما در نحوه ساخت ستون‌‌های محاسباتی و درک و استفاده از آن در نمودارهای  Power BI کمک خواهد کرد

به عنوان مثال، داده‌های شما دارای فیلدهای City و state است، اما شما می‌خواهید یک فیلد Location که هر دو را دربر دارد، مانند “Miami, FL” داشته باشید.  این دقیقا همان ستون محاسبه شده می‌باشد که از ترکیب داده‌های ۲ ستون به وجود آمده است.ستون‌های محاسباتی شبیه به معیارها(measures) هستند که هر دو مبتنی بر فرمول DAX می‌باشند اما نحوه استفاده آنها متفاوت است. اغلب measureهایی که در بخش Values استفاده می‌کنید، برای محاسبه نتایج بر اساس حوزه‌های دیگر می‌باشد. از ستون‌های محاسباتی به عنوان فیلدهای جدید در ردیف‌ها، محورها و بخش‌های گروهی نمودارها استفاده می‌شود.

پیش نیازها:

۱)این آموزش برای کاربران Power BI، جهت ایجاد مدل‌های پیشرفته‌تر طراحی شده است. لازم است از قبل بدانید که چگونه از Get Data و Power Query Editor برای وارد کردن داده‌ها استفاده کنید، با چندین جداول مرتبط کار کنید و فیلدها را به Report canvas اضافه کنید.

۲)فایل مورد نظر در این آموزش همان فایلی است که در آموزش نحوه ساخت معیار دلخواه در Power BI استفاده کردیم. این فایل شامل داده‌های فروش شرکت Contoso می‌باشد که از یک پایگاه داده استخراج شده  است. بنابراین شما قادر به اتصال به منبع داده یا مشاهده آن در Power Query Editor نخواهید بود. فایل را دانلود کرده، extract کنید و آن رادر Power BI باز کنید.

ایجاد یک ستون محاسباتی با مقادیری از جداول وابسته:

در این گزارش فروش، شما می خواهید دسته بندی محصولات و زیر شاخه ها را به عنوان مقادیر واحد نمایش دهید. مثل:

Cell phones – Accessories”, “Cell phones – Smartphones & PDAs”,…

هیچ فیلدی در لیست Fields وجود ندارد که این داده‌ها  را به شما بدهد، اما یک فیلد ProductCategory و یک فیلد ProductSubcategory وجود دارد که هر کدام در جدول خاص خود هستند. شما می‌توانید یک ستون محاسباتی ایجاد کنید که ترکیبی از مقادیر این دو ستون می‌باشد. فرمول های DAX می‌توانند قدرت کامل نفوذ در مدل شما را داشته باشند، که از جمله آن، ایجاد روابط بین جداول مختلف است.

۱- More options  (…)، را انتخاب کنید و  یا  اینکه در لیست فیلدها روی جدول ProductSubcategory راست کلیک کرده، و سپس ستون جدید را انتخاب کنید. در اینجا ستون جدید خود را در جدول ProductSubcategory ایجاد می کنید.

نوار فرمول در بالای صفحه گزارش نشان داده می شود و آماده است تا ستون خود را نامگذاری کنید و فرمول DAX را در آن وارد کنید.

۲- به طور پیش فرض، ستون محاسباتی جدید، Column نامیده می‌شود. اگر نام آن را تغییر ندهید، ستون‌های جدید دیگری را که اضافه کنید به صورت Column2، Column3 و غیره نام گذاری می‌شوند . برای اینکه به راحتی بتوانید ستون خود را شناسایی کنید، از آنجا که نام Column در نوار فرمول برجسته شده است، نام آن را به ProductFullCategory، تغییر دهیدو سپس یک علامت برابر (=) را تایپ کنید.

۳-مقادیر موجود در ستون جدید خود را با نام ProductCategory شروع کنید. از آنجا که این ستون در جدول متفاوت اما وابسته است، برای درک بهتر می‌توانید از تابع Related استفاده کنید.

بعد از علامت مساوی، r را وارد کنید. لیستی ظاهر می‌شود که تمامی توابع DAX که با حرف R شروع می‌شوند در آن وجود دارد. روی نام هر تابع  که بروید یک توضیح از طریقه عملکرد آن را نشان می‌دهد. همانطور که شما تایپ می‌کنید، لیست پیشنهادات به تابعی که نیاز دارید نزدیک‌تر می‌شود. Related را انتخاب کنید ، و سپس دکمه Enter را فشار دهید.

یک پرانتز باز ظاهر می‌شود، همراه با یک لیست پیشنهادات مربوط به ستون‌های وابسته‌ای که شما می‌توانید به تابع Related بدهید. و شما می‌توانید در آن شرح و جزئیاتی در مورد پارامترهای مورد انتظار مشاهده کنید.

۴-حال شما ستون ProductCategory را از جدول ProductCategory می‌خواهید. [ProductCategory[ProductCategory را انتخاب کنید Enter را فشار داده و سپس پرانتز بسته را تایپ کنید.

۵-برای اینکه فاصله‌ای بین مقادیر ProductCategories و ProductSubcategories بندازید، بعد از پرانتز بسته‌ی اولین عبارات، یک space، علامت (&)، دو نقل قول (“)، space، خط (-)، space دیگر، دو نقل قول دیگر و & را تایپ کنید. فرمول شما اکنون باید اینگونه باشد:

ProductFullCategory = RELATED(ProductCategory[ProductCategory]) & ” – ” &

۶-براکت باز ([) را تایپ کنید ، سپس ستون [ProductSubcategory] را برای تکمیل فرمول انتخاب کنید.

شما نیازی به استفاده از یک تابع Related دیگر برای فراخواندن جدول ProductSubcategory در عبارت دوم نداشتید، زیرا شما در حال ساخت ستون محاسباتی در این جدول هستید.

۷-فرمول را با فشار دادن Enter کامل کنید. فرمول معتبر است، و نام ستون ProductFullCategory در جدول ProductSubcategory در لیست فیلدها ظاهر می‌شود.

استفاده کردن از ستون جدید در گزارش:

حالا شما می‌توانید از ستون جدید خود یعنی ProductFullCategory برای دیدن SalesAmount توسط ProductFullCategory استفاده کنید.

۱-ستون ProductFullCategory را از جدول ProductSubcategory انتخاب کنید و یا آن را روی Report canvas بکشید تا جدولی حاوی تمام نام‌های ProductFullCategory را نشان دهد.

۲-فیلد SalesAmount را روی جدولSales  بکشید تا مبلغ فروش را برای هر دسته کامل محصول ببینید.

ایجاد ستون محاسباتی حاوی دستور IF:

در مثالContoso که شامل داده‌های فروش برای هر دو فروشگاه فعال و غیر فعال می‌باشد. با ایجاد فیلد Active StoreName می‌توانید اطمینان حاصل کنید که فروش فروشگاه‌های فعال به طور واضح از فروش فروشگاه غیر فعال در گزارشتان جدا شده است. در ستون محاسباتی جدید  یعنی همان Active StoreName، هر فروشگاه فعال با نام کامل فروشگاه ظاهر می‌شود، در حالی که فروشگاه‌های غیرفعال با هم در Inactive گروه‌بندی می‌شوند.

خوشبختانه، جدول فروشگاه دارای ستونی به نام Status است، با مقادیر “On” برای فروشگاه‌های فعال و “Off” برای فروشگاه‌های غیرفعال، که ما می‌توانیم برای ایجاد مقادیری برای ستون جدید StoreName Active استفاده کنیم. فرمول DAX شما از تابع IF برای آزمون وضعیت هر فروشگاه استفاده می‌کند و بسته به نتیجه، مقدار خاصی را برمی‌گرداند. اگر یک فروشگاه در وضعیت “On” باشد، فرمول نام فروشگاه را برمی‌گرداند. اگر وضعیت “Off” باشد، فرمول نام Active Store نام “غیر فعال” را تعیین می‌کند.

۱-یک ستون محاسباتی جدید را در جدول Stores ایجاد کنید و در نوار فرمول نام آن را Active StoreName قرار دهید.

۲- پس از علامت = شروع به تایپ IF بکنید. لیست پیشنهادات نشان می‌دهد که چه چیزی می ‌وانید اضافه کنید. IF را انتخاب کنید.

۳- اولین آرگومان برای IF یک آزمون منطقی است که آیا وضعیت فروشگاه “On” است یا خیر. براکت باز( [) را تایپ کنید، حال ستون‌های جدول Stores نمایش داده می‌شوند، [Status] را انتخاب کنید.

۴- درست بعد از[Status] ،= و سپس On را تایپ کنید، و سپس یک کاما (،) برای پایان دادن به بحث، تایپ کنید. راهنما (tooltip) نشان می‌دهد که در حال حاضر شما نیاز دارید یک مقداری را اضافه کنید تا  زمانی که نتیجه TRUE است آن را برگرداند.

۵- اگر وضعیت فروشگاه “On” باشد، نام فروشگاه نشان داده می شود. یک براکت باز ([) را تایپ کنید و ستون [StoreName] را انتخاب کنید و سپس یک کاما دیگر تایپ کنید. راهنما در حال حاضر نشان می‌دهد که شما نیاز به اضافه کردن یک مقدار دارید تا زمانی که نتیجه False می‌باشد آن مقدار برگردانده شود.

۶- برای اینکه این مقدار غیرفعال باشد، “Inactive” را تایپ کنید و سپس فرمول را با فشار دادن Enter یا انتخاب تیک موجود در نوار فرمول کامل کنید. فرمول معتبر است، و نام ستون جدید در جدول Stores در لیست فیلدها ظاهر می شود.

۷- شما می‌توانید از ستون Active StoreName مانند هر فیلد دیگری در نمودارها استفاده کنید. برای نشان دادن مقادیر فروش توسط Active StoreName، فیلد Active StoreName را انتخاب کنید یا آن را روی بوم بکشید و سپس Field SalesAmount را انتخاب کنید یا آن را به جدول بکشید. در این جدول، فروشگاه‌های فعال به صورت جداگانه نمایش داده می‌شوند، اما فروشگاه‌های غیرفعال در نهایت با هم به عنوان غیرفعال(Inactive) گروه‌بندی می‌شوند.

آنچه که فرا گرفته‌اید:

ستون‌های محاسباتی می‌توانند داده‌های شما را غنی‌سازی کرده و بینش ساده‌تری را ارائه دهند. شما آموخته‌اید که چگونه برای ایجاد ستون‌های محاسباتی در لیست فیلد و نوار فرمول، از لیست پیشنهادات و راهنمایی‌های استفاده از فرمول‌های خود استفاده کنید،  توابع DAX مانند Related و IF با آرگومان‌های مناسب را فراخوانی کرده ، از ستون‌های محاسبه شده خود در نمودارها استفاده کنید.