آموزش Power BI صفر تا سکو : قسمت نهم (توابع X Aggregation)

در ادامه آموزش سریالی Power BI به آموزش زبان DAX و بررسی توابع X Aggregation می پردازیم. این توابع، محدودیت های توابع تجمیعی ساده مانند SUM، MAX، MIN، AVERAGE را برطرف کرده است. زبان DAX شامل تعداد زیادی از این توابع است. توابعی مانند SUMX، MINX، MAXX، AVERAGEX و COUNTX در دسته بندی این توابع قرار می گیرند.

توابع تجمیعی در Power BI

محدودیت

فرض کنید می خواهیم میزان فروش را در قالب یک Card نمایش دهیم. برای این منظور نیاز داریم تا از تابع SUM استفاده کنیم. در واقع باید میزان فروش در هر فاکتور را محاسبه و با استفاده از تابع SUM آن ها را جمع کنیم. میزان فروش در هر فاکتور از ضرب تعداد کالای خریداری شده در قیمت هر واحد کالا به دست می آید. در مجموعه داده ما تعداد کالای خریداری شده در ستون Order Quantity و قیمت کالا در ستون Unit Price ذخیره شده است.

حال بیایید میزان فروش را حساب کنیم. همان طور که در تصویر مشاهده می نمایید معرفی هیچ ورودی جز یک ستون برای تابع SUM امکان پذیر نمی باشد. در واقع شما قادر به فرمول نویسی در این تابع نیستید.

پس برای ایجاد این مژر با استفاده از تابع SUM مجبور به انجام یک کار نه چندان خوب هستیم! افزودن یک ستون محاسباتی به جدول. بارها و بارها در مطالب قبل به این موضوع اشاره شده است که تا جایی که مجبور نشده اید، نباید ستون محاسباتی به جداول اضافه کنید. اما فعلا این کار غلط را انجام می دهیم.

Calculated Column یا Measure؟

به جدول فروش می رویم و یک ستون محاسباتی ایجاد می کنیم. در این ستون محاسباتی به راحتی تعداد را در قیمت ضرب می کنیم. در نهایت یک ستون به صورت کاملا فیزیکی در جدول ایجاد می شود که در هر ردیف تعداد را در قیمت ضرب می کند.

LineAmount = Sales[OrderQuantity] * Sales[UnitPrice]

حال با استفاده از تابع SUM و ستون جدید ایجاد شده محاسبه فروش کاملا قابل دست یابی است.

SalesAmount = SUM(Sales[LineAmount])

توابع X Aggregation

تابع SUMX

توابع X Aggregation یک قابلیت بسیار شگفت انگیز و مفید در خود به همراه دارند. این قابلیت بسیار خوب، اسکن کردن ردیف به ردیف جدول و اجرای فرمول مورد نیاز ما در هر ردیف جدول و سپس اجرای عملیات تجمیعی بر روی آن ها است. در واقع این توابع شما را از ایجاد ستون محاسباتی بی نیاز می کند.

تنها استفاده ما از ستون محاسباتی این بود که ردیف به ردیف تعداد را در قیمت ضرب کند و این ستون قابل معرفی به تابع SUM باشد. ولی ما به جای این کار می توانیم از تابع SUMX استفاده کنیم. ما یک جدول و یک فرمول به این تابع معرفی می کنیم. این فرمول ردیف به ردیف اجرا شده و در نهایت نتایج توسط تابع SUM جمع می شوند. در این روش نیاز به ایجاد ستون محاسباتی نیست.

در واقع ابتدا جدول ردیف به ردیف اسکن می شود و برای هر ردیف تعداد را در قیمت ضرب می کند. در نهایت تمامی این نتایج جمع شده و عدد 29.36 میلیون نمایش داده می شود.

SalesAmountCorrect = SUMX( Sales,Sales[OrderQuantity]* Sales[UnitPrice])

تابع COUNTX

تابع COUNT تابعی است که تعداد ردیف های یک ستون را برای ما نمایش می دهد. به طور مثال فرض کنید می خواهیم تعداد ردیف های ستون کد مشتری از جدول مشتریان را محاسبه کنیم تا تعداد کل مشتریان را به دست بیاوریم. این تابع تعداد کل مشتریان را بر می گرداند. تعداد کل مشتریان ما 18000 نفر است.

CustomerNumber = COUNT(Customer[CustomerKey])

حال فرض کنید می خواهیم تعداد مشتریانی که دارای فرزند هستند را به دست آوریم. برای این منظور باید از یک جدول با ردیف های محدود شده استفاده کنیم. برای این منظور از تابع FILTER استفاد می کنیم.

CustomerWithChildren = COUNTX(FILTER(Customer,Customer[TotalChildren]>0),Customer[CustomerKey])

در واقع باید بدانید که شما نباید حتما یک جدول موجود در مدل را به عنوان ورودی اول به تابع معرفی کنید، بلکه می توانید یک جدول محدود شده را به عنوان ورودی اول معرفی کند. در واقع ابتدا تابع FILTER کل جدول را اسکن می کند. تمامی ردیف های جدول مشتریان که دارای فرزند هستند به عنوان نتیجه فیلتر باقی می مانند. سپس تابع COUNTX ردیف های این جدول را شمارش می کند.

تابع AVERAGEX

فرض کنید می خواهیم میانگین جمع هزینه حمل و هزینه مالیات را محاسبه کنیم. کافیست با استفاده از تابع AVERAGEX این عملیات را انجام دهیم. جدول فروش را به تابع معرفی می کنیم و می خواهیم برای هر ردیف از جدول میزان هزینه حمل و مالیات را جمع و در نهایت از آن ها میانگین بگیرد.

Tax&freight = AVERAGEX(Sales, Sales[Freight] + Sales[TaxAmt])

تابع MINX و MAXX

فرض کنید می خواهیم کمترین و بیشترین قیمت از کالای قرمز رنگ را استخراج کنیم. برای این منظور باید ابتدا جدول کالا را به کالاهای قرمز رنگ محدود کرده و با استفاده از تابع MINX و MAXX کمترین و بیشترین قیمت را استخراج کنید.

MinPrice = MINX( FILTER(‘Product’, ‘Product'[Color] = “Red”), ‘Product'[ListPrice])