0

مطالب آموزشی

تحلیل داده | هوش تجاری | آموزش Power BIمطالب آموزشیDAXپیدا کردن محصولاتی که در هر سال جزو ۱۰ محصول برتر هستند با DAX در Power BI

پیدا کردن محصولاتی که در هر سال جزو ۱۰ محصول برتر هستند با DAX در Power BI

آبان 14, 1404
ارسال شده توسط
DAX ، Power BI ، آموزش های متنی
101 بازدید

به طور کلی، برای پیدا کردن محصولات برتر در Power BI معمولاً از توابعی مانند GENERATE و TOPN استفاده می‌شود.
اما یک حالت جالب و کاربردی از این سناریو وجود دارد که به حل یک مسئله‌ی خاص تجاری کمک می‌کند.

در این حالت، پس از آنکه ۱۰ محصول برتر هر سال را شناسایی کردیم، می‌خواهیم فقط محصولاتی را فیلتر کنیم که در بیشتر سال‌ها در میان ۱۰ محصول برتر ظاهر شده‌اند.

دریافت چنین فهرستی به ما کمک می‌کند تا محصولات اصطلاحاً Evergreen (همیشه‌سبز) را بشناسیم — یعنی محصولاتی که به‌صورت مداوم در فهرست پرفروش‌ترین‌ها باقی می‌مانند.

شکل زیر (در مقاله اصلی) فهرست ۱۰ محصول برتر در هر سال را نشان می‌دهد. در این تصویر، محصولاتی که حداقل در چهار سال از پنج سال اخیر جزو ۱۰ محصول برتر بوده‌اند، مشخص شده‌اند.

DAXPOWERBI1

به‌دست آوردن فهرست محصولات برتر مانند Bottle، Carton و Tray کمی پیچیده‌تر است،
زیرا نیاز به دست‌کاری و پردازش اضافی روی جداول دارد و هنگام ایجاد ساختارهای موقت باید دقت بیشتری به خرج داد.

هدف ما این است که از میان فهرست محصولات برتر هر سال،
به نتیجه‌ای برسیم که تنها محصولاتی را نشان دهد که در چندین سال متوالی جزو پرفروش‌ترین‌ها بوده‌اند.

به بیان دیگر، خروجی مورد انتظار ما از میان لیست کل محصولات، چیزی شبیه به فهرست زیر است:

Bottle, Carton, Tray

این سه محصول نمونه‌ای از محصولات همیشه‌سبز (Evergreen Products) هستند — محصولاتی که در طول سال‌های مختلف همواره در بین ۱۰ محصول برتر از نظر فروش باقی مانده‌اند.

DAXPOWERBI2

برای اهداف آموزشی، مفید است که کل فرآیند ایجاد این Measure را مرحله‌به‌مرحله بررسی کنیم،
زیرا برخی از جزئیات فقط زمانی قابل درک هستند که کل روند طراحی و توسعه دیده شود.

به همین دلیل، مراحل زیر را دنبال می‌کنیم:

  1. ایجاد یک Query برای استخراج محصولات
    در ابتدا یک Query ایجاد می‌کنیم تا محصولات را به‌دست آوریم.
    این مرحله به ما کمک می‌کند تا با مشاهده‌ی نتایج میانی، کد را بررسی و رفع خطا (Debug) کنیم.

  2. تبدیل Query به Measure
    در این مرحله، Query اولیه را به یک Measure تبدیل می‌کنیم.
    تمرکز ما در این گام بر این است که کد در هر Filter Context (زمینه فیلتر) به درستی عمل کند.

  3. انتقال کد از Measure به Function
    پس از اطمینان از صحت Measure، منطق آن را به یک Function منتقل می‌کنیم.
    این کار باعث می‌شود بتوانیم از همان منطق در چند Measure مختلف استفاده کنیم و در نهایت،
    هم تعداد محصولات برتر و هم میزان فروش آن‌ها را محاسبه نماییم.

مزیت کلیدی استفاده از Measure

بزرگ‌ترین مزیت استفاده از Measure این است که فهرست محصولات به‌صورت داینامیک (پویا) تولید می‌شود
و به‌راحتی می‌توان آن را در گزارش‌ها فیلتر کرد.

برای مثال، تصویر زیر رتبه‌بندی محصولات را بین سال‌های ۲۰۲۰ تا ۲۰۲۴ نشان می‌دهد:

  • ۸ محصول در حداقل ۴ سال از ۵ سال گذشته جزو ۱۰ محصول برتر بوده‌اند.

  • و تنها ۱ محصول در تمام سال‌ها همواره در بین ۱۰ محصول برتر باقی مانده است — همان آخرین محصول در فهرست.

DAXPOWERBI3

الگوریتمی که برای شناسایی محصولات برتر استفاده می‌کنیم، نیاز به کمی تفکر منطقی و طراحی مرحله‌به‌مرحله دارد.
در گام نخست، باید جدولی آماده کنیم که شامل ۱۰ محصول برتر در هر سال باشد.

اگر در داده‌های نمونه‌ (Demo Data) خود اطلاعات مربوط به ۱۰ سال داشته باشیم،
خروجی این جدول باید حدوداً شامل ۱۰۰ سطر (۱۰ محصول × ۱۰ سال) باشد.

این گام، اولین مرحله از فرآیند تحلیل است و پایه‌ی محاسبات بعدی را تشکیل می‌دهد.

				
					EVALUATE
VAR NumOfTop = 10
VAR Years =
    SUMMARIZE ( Sales, 'Date'[Year] )
VAR YearsAndTop10 =
    GENERATE (
        Years,
        TOPN ( NumOfTop, VALUES ( 'Product'[ProductKey] ), [Sales Amount] )
    )
RETURN
    YearsAndTop10

نتیجه‌ی اجرای این Query شامل دو ستون اصلی است:

  1. Year (سال)

  2. Product Key (کلید محصول)

در این جدول، برای هر سال ۱۰ کلید محصول وجود دارد،
که همان ۱۰ محصول برتر آن سال از نظر فروش هستند.

به‌عبارت دیگر، این جدول خروجی پایه‌ای است که در مراحل بعدی از آن برای تحلیل و شناسایی محصولات همیشه‌سبز (Evergreen Products) استفاده خواهیم کرد.

در مرحله‌ی بعد، باید جدول YearsAndTop10 را بر اساس ستون Product گروه‌بندی کنیم
و برای هر محصول، تعداد ردیف‌ها (یا سال‌هایی) را که آن محصول در میان ۱۰ محصول برتر بوده، محاسبه کنیم.

از آنجا که در این مرحله قصد داریم یک متغیر (Variable) را گروه‌بندی کنیم، نه یک جدول مدل (Model Table)،
باید از تابع GROUPBY استفاده کنیم.

(برای جزئیات بیشتر، به مقاله‌ی تفاوت بین GROUPBY و SUMMARIZE مراجعه کنید.)

هدف ما این است که برای هر Product Key، تعداد سال‌هایی که آن محصول در بین ۱۰ محصول برتر قرار گرفته است را محاسبه کنیم.

اما نکته مهم این است که در تابع GROUPBY نمی‌توان از COUNTROWS به‌صورت مستقیم به عنوان تابع تجمعی (Aggregation Function) استفاده کرد،
زیرا GROUPBY نیاز دارد که از تابع CURRENTGROUP() برای پیمایش (Iteration) زیرمجموعه ردیف‌های گروه‌بندی‌شده استفاده کنیم.

بنابراین، باید از ترکیب زیر استفاده کنیم:

  • SUMX برای جمع‌زدن ردیف‌ها

  • و مقدار ثابت 1 برای شمارش هر ردیف.

				
					EVALUATE
VAR NumOfTop = 10
VAR Years =
    SUMMARIZE ( Sales, 'Date'[Year] )
VAR YearsAndTop10 =
    GENERATE (
        Years,
        TOPN ( NumOfTop, VALUES ( 'Product'[ProductKey] ), [Sales Amount] )
    )
VAR ProdsAndCount =
    GROUPBY (
        YearsAndTop10,
        'Product'[ProductKey],
        "NumOfYears", SUMX ( CURRENTGROUP (), 1 )
    )
RETURN
    ProdsAndCount

نتیجه‌ی اجرای گام دوم، جدولی است که شامل دو ستون زیر می‌باشد:

  1. Product Key (کلید محصول)

  2. Number of Years (تعداد سال‌ها)

در این جدول، برای هر محصول مشخص می‌شود که در چند سال جزو ۱۰ محصول برتر بوده است.

در گام نهایی، باید ردیف‌هایی را از جدول حذف کنیم که در آن‌ها مقدار ستون NumOfYears به اندازه کافی بزرگ نیست تا محصول را جزو محصولات برتر محسوب کنیم.

برای این منظور:

  1. در ابتدا از یک متغیر (Variable) برای شمارش کل تعداد سال‌ها استفاده می‌کنیم.

  2. سپس متغیری به نام Coverage تعریف می‌کنیم تا مشخص شود ما به محصولاتی علاقه‌مندیم که حداقل در ۸۰٪ از کل سال‌ها در بین ۱۰ محصول برتر ظاهر شده باشند.

با استفاده از یک عبارت FILTER که بر اساس این متغیرها نوشته شده است،
می‌توانیم به نتیجه‌ی نهایی برسیم و فقط محصولاتی را نگه داریم که معیار ما را برآورده می‌کنند.

به‌عبارت دیگر، خروجی این مرحله شامل محصولاتی است که:

  • در اکثر سال‌ها (مثلاً ۸۰٪ یا بیشتر) جزو ۱۰ محصول برتر بوده‌اند،

  • و می‌توان آن‌ها را به عنوان محصولات همیشه‌سبز (Evergreen Products) در تحلیل‌های فروش شناسایی کرد.

				
					EVALUATE
VAR NumOfTop = 10
VAR Coverage = 0.8
VAR Years =
    SUMMARIZE ( Sales, 'Date'[Year] )
VAR NumOfYears =
    COUNTROWS ( Years )
VAR YearsAndTop10 =
    GENERATE (
        Years,
        TOPN ( NumOfTop, VALUES ( 'Product'[ProductKey] ), [Sales Amount] )
    )
VAR ProdsAndCount =
    GROUPBY (
        YearsAndTop10,
        'Product'[ProductKey],
        "NumOfYears", SUMX ( CURRENTGROUP (), 1 )
    )
VAR MinYears = NumOfYears * Coverage
VAR BestProds =
    FILTER ( ProdsAndCount, [NumOfYears] >= MinYears )
RETURN
    BestProds

نتیجه نهایی اجرای این Query، جدولی به نام BestProds است که شامل ۵ ردیف می‌باشد.

این جدول تنها محصولاتی را نشان می‌دهد که:

  • در حداقل ۸۰٪ از سال‌ها جزو ۱۰ محصول برتر بوده‌اند،

  • یعنی محصولاتی که می‌توان آن‌ها را به عنوان محصولات همیشه‌سبز (Evergreen Products) شناسایی کرد.

گام اول تکمیل شد و ما یک Query ایجاد کردیم که فهرست بهترین محصولات را تولید می‌کند. گام بعدی، ادغام این کد در یک Measure است تا بتوانیم از این فهرست برای فیلتر کردن محصولات در گزارش‌ها استفاده کنیم.

همان‌طور که مشاهده خواهی کرد، در این مرحله باید به وجود Filter Context توجه کنیم،زیرا کدی که تا اینجا نوشته‌ایم، در یک Query اجرا می‌شود که هیچ زمینه فیلتر خارجی (External Filter Context) ندارد.

اما یک Measure باید طوری طراحی شود که در هر Filter Context به درستی کار کند.به عبارت دیگر، خروجی Measure باید پویا و قابل فیلتر شدن در گزارش‌ها باشد،و بر اساس فیلترهای اعمال‌شده توسط کاربر، نتایج را به‌روز کند.

اولین تلاش برای تبدیل Query به Measure، انتقال کد Query به داخل یک Measure است.سپس می‌توانیم از جدول BestProds به‌عنوان فیلتر در تابع CALCULATE استفاده کنیم تا تعداد محصولات را محاسبه کنیم.

				
					Num Best Prods =
VAR NumOfTop = 10
VAR Coverage = 0.8
VAR Years =
    SUMMARIZE ( Sales, 'Date'[Year] )
VAR NumOfYears =
    COUNTROWS ( Years )
VAR YearsAndTop10 =
    GENERATE (
        Years,
        TOPN ( NumOfTop, VALUES ( 'Product'[ProductKey] ), [Sales Amount] )
    )
VAR ProdsAndCount =
    GROUPBY (
        YearsAndTop10,
        'Product'[ProductKey],
        "NumOfYears", SUMX ( CURRENTGROUP (), 1 )
    )
VAR MinYears = NumOfYears * Coverage
VAR BestProds =
    FILTER ( ProdsAndCount, [NumOfYears] >= MinYears )
VAR Result = CALCULATE ( COUNTROWS ( 'Product' ), BestProds )
RETURN
    Result

این Measure به درستی کار نمی‌کند، زیرا به جای اینکه فقط محصولات برتر را شمارش کند،
برای هر محصول، مقدار ۱ بازمی‌گرداند.به عبارت دیگر، این Measure فقط تعداد ردیف‌ها را بدون فیلتر کردن بر اساس جدول BestProds محاسبه می‌کند و نتیجه‌ی آن با هدف اصلی ما که شمارش فقط محصولات برتر است، همخوانی ندارد.

DAXPOWERBI4

مشکل اصلی این است که Measure در یک Filter Context اجرا می‌شود که بر چندین ارزیابی متغیر تاثیر می‌گذارد.
به طور خاص:

  • بر متغیر Years تأثیر می‌گذارد، زیرا تنها فروش‌های فیلترشده کنونی را در نظر می‌گیرد.

  • بر جدول YearsAndTop10 تأثیر می‌گذارد، زیرا TOPN هنوز مقدار Sales Amount را در همان Filter Context فعلی ارزیابی می‌کند.

همچنین یک مشکل ظریف دیگر در داخل CALCULATE وجود دارد،
اما در این بخش آن را کنار می‌گذاریم و بعداً به آن می‌پردازیم.

برای حل مشکلات اولیه، می‌توانیم تعریف متغیرها را در داخل تابع ALLSELECTED قرار دهیم تا:

  • بهترین محصولات را صرف‌نظر از هر فیلتر موجود در ماتریس یا گزارش پیدا کنیم،

  • و محاسبه Measure را به صورت پویا و مستقل از فیلترهای فعلی انجام دهیم.

به عبارت دیگر، با استفاده از ALLSELECTED، می‌توانیم جدول BestProds را به گونه‌ای تعریف کنیم که
تمام محصولات برتر را پیدا کند و محدود به فیلترهای اعمال‌شده توسط کاربر نشود.

				
					Num Best Prods =
VAR NumOfTop = 10
VAR Coverage = 0.8
VAR Years =
    CALCULATETABLE (
        SUMMARIZE ( Sales, 'Date'[Year] ),
        ALLSELECTED ()
    )
VAR NumOfYears =
    COUNTROWS ( Years )
VAR YearsAndTop10 =
    CALCULATETABLE (
        GENERATE (
            Years,
            TOPN ( NumOfTop, VALUES ( 'Product'[ProductKey] ), [Sales Amount] )
        ),
        ALLSELECTED ()
    )
VAR ProdsAndCount =
    GROUPBY (
        YearsAndTop10,
        'Product'[ProductKey],
        "NumOfYears", SUMX ( CURRENTGROUP (), 1 )
    )
VAR MinYears = NumOfYears * Coverage
VAR BestProds =
    FILTER ( ProdsAndCount, [NumOfYears] >= MinYears )
VAR Result = CALCULATE ( COUNTROWS ( 'Product' ), BestProds )
RETURN
    Result

با اعمال تغییرات و استفاده از ALLSELECTED در تعریف متغیرها،اکنون Measure در ماتریس به‌خوبی کار می‌کند.

DAXPOWERBI5

اکنون زمان آن رسیده که با آن مشکل ظریف که قبلاً اشاره کردیم، برخورد کنیم.

  • جدول BestProds شامل ستون Product[ProductKey] است،
    که توسط CALCULATE به Filter Context اضافه می‌شود.

  • ستون Product[ProductKey] با Filter Context موجود که توسط ماتریس ایجاد شده (مثلاً با ستون‌های Product[Brand] و Product[Product Name]) تداخلی ندارد.

اما اگر به هر دلیلی کاربر از ستون Product[ProductKey] در گزارش استفاده کند،
نتیجه Measure غلط خواهد بود،
زیرا فیلتر اعمال‌شده توسط BestProds می‌تواند فیلتر ایجادشده توسط ماتریس را بازنویسی (Override) کند.

به عبارت دیگر، این مشکل زمانی رخ می‌دهد که ستون کلید محصول هم در فیلتر گزارش و هم در Measure استفاده شود.
در چنین شرایطی، Measure ممکن است محصولات نادرست را محاسبه کند و نتایج تحلیل دقیق نباشد.

DAXPOWERBI6

حل این مشکل بسیار ساده است:
کافی است تابع KEEPFILTERS را دور جدول BestProds در CALCULATE قرار دهیم تا Filter Context خارجی (Outer Filter) جایگزین نشود.

به عبارت دیگر، با استفاده از KEEPFILTERS:

  • فیلترهای موجود در ماتریس یا گزارش حفظ می‌شوند،

  • و Measure فقط محصولات موجود در BestProds را در نظر می‌گیرد،

  • بدون اینکه فیلترهای دیگر گزارش بازنویسی (Override) شوند.

				
					Num Best Prods = 
VAR NumOfTop = 10
VAR Coverage = 0.8
VAR Years =
    CALCULATETABLE (
        SUMMARIZE ( Sales, 'Date'[Year] ),
        ALLSELECTED ()
    )
VAR NumOfYears =
    COUNTROWS ( Years )
VAR YearsAndTop10 =
    CALCULATETABLE (
        GENERATE (
            Years,
            TOPN ( NumOfTop, VALUES ( 'Product'[ProductKey] ), [Sales Amount] )
        ),
        ALLSELECTED ()
    )
VAR ProdsAndCount =
    GROUPBY (
        YearsAndTop10,
        'Product'[ProductKey],
        "NumOfYears", SUMX ( CURRENTGROUP (), 1 )
    )
VAR MinYears = NumOfYears * Coverage
VAR BestProds =
    FILTER ( ProdsAndCount, [NumOfYears] >= MinYears )
VAR Result = CALCULATE ( COUNTROWS ( 'Product' ), KEEPFILTERS ( BestProds ) )
RETURN
    Result

Measure نهایی کمی پیچیده است، اما اکنون که کاربران می‌توانند تعداد محصولات برتر را ببینند،
احتمالاً به حجم فروش این محصولات نیز علاقه‌مند خواهند شد.

به همین دلیل، بهترین راه‌حل این است که یک Function ایجاد کنیم که:

  1. Measure یا عبارت محاسباتی را به عنوان آرگومان دریافت کند،

  2. سپس فقط برای محصولات برتر آن Measure را محاسبه کند و نتیجه را بازگرداند.

نوشتن کد Function

این Function حداقل به یک آرگومان نیاز دارد:

  • Expression to Evaluate: عبارتی که می‌خواهیم محاسبه شود.

برای اهداف آموزشی، ما آرگومان دومی نیز اضافه می‌کنیم:

  • Expression for TOPN: عبارتی که در داخل تابع TOPN استفاده می‌شود تا محصولات برتر را مشخص کند،به طوری که بتوانیم محصولات برتر را نه فقط بر اساس Sales Amount، بلکه بر اساس هر Measure دیگری نیز پیدا کنیم.

با این روش، Function ما پویا و قابل استفاده مجدد خواهد بود و می‌توان از آن برای:

  • محاسبه تعداد محصولات برتر،

  • محاسبه میزان فروش یا هر شاخص دیگر،

  • در هر Filter Context و گزارش،استفاده کرد.

				
					Local.ComputeForBestProds = ( computeExpr: EXPR, sortExpr : EXPR ) =>
    VAR NumOfTop = 10
    VAR Coverage = 0.8
    VAR Years =
        CALCULATETABLE (
            SUMMARIZE ( Sales, 'Date'[Year] ),
            ALLSELECTED ( )
        )
    VAR NumOfYears =
        COUNTROWS ( Years )
    VAR YearsAndTop10 =
        CALCULATETABLE (
            GENERATE (
                Years,
                TOPN ( NumOfTop, VALUES ( 'Product'[ProductKey] ), CALCULATE ( sortExpr ) )
            ),
            ALLSELECTED ()
        )
    VAR ProdsAndCount =
        GROUPBY (
            YearsAndTop10,
            'Product'[ProductKey],
            "NumOfYears", SUMX ( CURRENTGROUP (), 1 )
        )
    VAR MinYears = NumOfYears * Coverage
    VAR BestProds =
        FILTER ( ProdsAndCount, [NumOfYears] >= MinYears )
    VAR Result = CALCULATE ( computeExpr, KEEPFILTERS ( BestProds ) )
    RETURN
        Result

قابل توجه است که آرگومان SortExpr، زمانی که در داخل تابع TOPN استفاده می‌شود،
با CALCULATE احاطه می‌شود تا اطمینان حاصل شود که Function به‌صورت روان و صحیح عمل می‌کند،
صرف‌نظر از آرگومان‌هایی که دریافت می‌کند.

استفاده مجدد از Function در چند Measure

پس از ایجاد این Function، امکان استفاده از آن در چندین Measure مختلف وجود دارد.
این کار مزایای زیر را دارد:

  • جلوگیری از تکرار کد،

  • مدیریت آسان‌تر و نگهداری ساده‌تر Measureها،

  • امکان استفاده از یک منطق یکسان برای محاسبه تعداد محصولات برتر، میزان فروش و سایر شاخص‌ها،

  • و حفظ پویا بودن محاسبات در هر Filter Context و گزارش.

به این ترتیب، Function به ابزاری قدرتمند و قابل انعطاف برای تحلیل محصولات برتر تبدیل می‌شود.

				
					Num Best Prods =
    Local.ComputeForBestProds ( COUNTROWS ( 'Product' ), [Sales Amount] )
				
			
				
					Sales Best Prods =
    Local.ComputeForBestProds ( [Sales Amount], [Sales Amount] )

دو Measure که از این Function استفاده می‌کنند، اکثر منطق کسب‌وکار (Business Logic) را به اشتراک می‌گذارند.

نتیجه‌گیری

نوشتن کد DAX غیرساده نیازمند یک رویکرد است که مشکلات احتمالی را به حداقل برساند.

مراحل پیشنهادی برای توسعه چنین کدهایی به شرح زیر است:

  1. تعریف و دیباگ کد در Query
    اگر یک Measure نیاز به دست‌کاری جداول دارد، بهترین گزینه این است که ابتدا کد را در قالب یک Query تعریف و اشکال‌زدایی (Debug) کنیم.

  2. انتقال کد به Measure
    پس از آنکه منطق کسب‌وکار (Business Logic) به خوبی تعریف شد، کد را به یک Measure منتقل می‌کنیم و هر مشکلی که از Filter Context خارجی ناشی می‌شود، اصلاح می‌کنیم.

  3. استفاده از Function برای منطق مشترک
    زمانی که Measure به درستی کار کرد و منطق آن می‌تواند در چندین Measure دیگر نیز استفاده شود،
    تعریف یک Function همیشه گزینه خوبی است، زیرا به توسعه‌دهندگان امکان استفاده مجدد از کد را می‌دهد و نگهداری آن را آسان‌تر می‌کند.

با پیروی از این رویکرد، می‌توان کدهای DAX پیچیده و پویا نوشت که:

  • قابل نگهداری باشند،

  • در گزارش‌ها و ماتریس‌ها درست عمل کنند،

  • و منطق کسب‌وکار را به شکل موثری اجرا نمایند.

اشتراک گذاری:
برچسب ها:
در تلگرام
کانال ما را دنبال کنید!
در اینستاگرام
ما را دنبال کنید!
در یوتوب
ما را دنبال کنید!
Created by potrace 1.14, written by Peter Selinger 2001-2017
در آپارات
ما را دنبال کنید!

مطالب زیر را حتما مطالعه کنید

دیدگاهتان را بنویسید

آموزش تخصصی تحلیل داده و هوش تجاری

اگر به حوزه های تحلیل داده , هوش تجاری (BI) و مصورسازی داده ها و ابزارهایی مانند Power BI علاقه مند هستید, با ثبت ایمیل خود به جمع مخاطبان ما بپیوندید. ما به صورت منظم , مطالب آموزشی , نکات فنی و کاربردی و منابع تخصصی را برای شما ارسال خواهیم کرد.