ایجاد TopPercent در زبان Dax

پیشنیاز : آشنایی با زبان Dax

مدت زمان مورد نیاز برای مطالعه : ۲۰ دقیقه

لینک ابزار : پاور بی آی نسخه دسکتاپ

لینک ابزار جانبی : Dax Studio

هدف

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

به طور مثال فرض کنید که میخواهید پس از یک سال فروش محصولات خود یک بررسی داشته باشید که ۱۰ درصد بیشترین فروش از چه محصولاتی بوده است.این تحلیل میتواند به صاحب کسب و کار کمک که بیزینس خود را به سمت فروش محصولات پر فروش سوق دهد و متمرکزتر عمل کند.

یکی از متد هایی که در زبان MDX برای انجام این کار طراحی شده است متد TopPercent می باشد. اما از بخت بد! این متد در زبان Dax وجود ندارد.

در این مقاله سعی داریم که با روش هایی این متد را در زبان Dax پیاده سازی کنیم تا بتوان از آن استفاده کرد.

پس بیایید قدم به قدم با هم پیش برویم تا به هدف نهایی برسیم…

پیشنهاد میشود برای پیاده سازی دستورات Dax حتما از نرم افزار Dax Studio  استفاده کنید.

حتما در مقالات بعدی سعی میکنم توضیحات مختصری در ارتباط با این نرم افزار نیز ارائه دهم

میخواهیم در مرحله اول اطلاعات مربوط به جدول محصولات خود را مشاهده کنیم

EVALUATE
DimPart

در مرحله بعدی میخواهیم اطلاعات نام محصولات را به صورت غیر تکراری نمایش دهیم

EVALUATE
VALUES ( DimPart[LocalizedName] )

در مرحله بعد میخواهیم میزان فروش را به ازای هر محصول بدست آوریم

EVALUATE
ADDCOLUMNS (
    VALUES ( DimPart[LocalizedName] ),
    "@Amt", [FactSpExchangePart Amount]
)

هماهنطور که ملاحظه میکنید میزان فروش(@Amt) به هیچ عنوان مرتب نمیباشد.جهت انجام این کار از دستور Order By استفاده میکنیم.

EVALUATE
ADDCOLUMNS (
    VALUES ( DimPart[LocalizedName] ),
    "@Amt", [FactSpExchangePart Amount]
)
ORDER BY [@Amt] DESC

مجددا در مرحله بعدی مقدار ایجاد شده را در یک متغیر قرار میدهیم

EVALUATE
VAR prodsAnsSales =
    ADDCOLUMNS (
        VALUES ( DimPart[LocalizedName] ),
        "@Amt", [FactSpExchangePart Amount]
    )
RETURN
    prodsAnsSales
ORDER BY [@Amt] desc    

حال در این مرحله قصد داریم رنکینگ میزان فروش را به ازای هر محصول  بدست بیاوریم

EVALUATE
VAR prodsAnsSales =
    ADDCOLUMNS (
        VALUES ( DimPart[LocalizedName] ),
        "@Amt", [FactSpExchangePart Amount]
    )
VAR prodsAndSalesRT =
    ADDCOLUMNS (
        prodsAnsSales,
        "@AmtRT",
            VAR CurrentAmt = [@Amt]
            VAR HigherValues =
                FILTER ( prodsAnsSales, [@Amt] >= CurrentAmt )
            RETURN
                COUNTROWS ( HigherValues )
    )
RETURN
    prodsAndSalesRT
ORDER BY [@Amt] DESC

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

EVALUATE
VAR prodsAnsSales =
    ADDCOLUMNS (
        VALUES ( DimPart[LocalizedName] ),
        "@Amt", [FactSpExchangePart Amount]
    )
VAR prodsAndSalesRT =
    ADDCOLUMNS (
        prodsAnsSales,
        "@AmtRT",
            VAR CurrentAmt = [@Amt]
            VAR HigherValues =
                FILTER ( prodsAnsSales, [@Amt] >= CurrentAmt )
            VAR Result =
                SUMX ( HigherValues, [@Amt] )
            RETURN
                Result
    )
RETURN
    prodsAndSalesRT
ORDER BY [@Amt] DESC

همانطور که ملاحظه میکنید در ستون @AmtRT هر رکورد جمع میزان فروش خودش بعلاوه رکوردهای فروش بالاتر را در خود دارد.

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

EVALUATE

VAR prodsAnsSales =
    ADDCOLUMNS (
        VALUES ( DimPart[LocalizedName] ),
        "@Amt", [FactSpExchangePart Amount]
    )
VAR TotlSales =
    SUMX ( prodsAnsSales, [@Amt] )
VAR prodsAndSalesRT =
    ADDCOLUMNS (
        prodsAnsSales,
        "@AmtRT%",
            VAR CurrentAmt = [@Amt]
            VAR HigherValues =
                FILTER ( prodsAnsSales, [@Amt] >= CurrentAmt )
            VAR Result =
                DIVIDE ( SUMX ( HigherValues, [@Amt] ), TotlSales )
            RETURN
                round(Result*100,2) 
    )
RETURN
    prodsAndSalesRT
ORDER BY [@Amt] DESC

همانطور که در تصویر مشاهده میکنید درصد فروش هرچقدر به پایین می آید تکمیل تر شده و به ۱۰۰ نزدیکتر میشود

حال در مرحله بعدی میخواهیم با اضافه کردن متغیر جدیدی به طور مثال ۲۰ درصد اول بیشترین محصولات فروخته شده را متوجه متوجه شویم.

EVALUATE
Var PercToUse = 20
VAR prodsAnsSales =
    ADDCOLUMNS (
        VALUES ( DimPart[LocalizedName] ),
        "@Amt", [FactSpExchangePart Amount]
    )
VAR TotlSales =
    SUMX ( prodsAnsSales, [@Amt] )
VAR prodsAndSalesRT =
    ADDCOLUMNS (
        prodsAnsSales,
        "@AmtRT%",
            VAR CurrentAmt = [@Amt]
            VAR HigherValues =
                FILTER ( prodsAnsSales, [@Amt] >= CurrentAmt )
            VAR Result =
                DIVIDE ( SUMX ( HigherValues, [@Amt] ), TotlSales )
            RETURN
                round(Result*100,2) 
    )
var Result = FILTER(prodsAndSalesRT,[@AmtRT%] <= PercToUse)
RETURN
    Result
ORDER BY [@Amt] DESC

از اینجا به بعد باید دستوراتی که در Dax Studio  تولید کردیم را باید در مدل داده ای خود با اعمال تغییرانی اضافه کنیم

برای انجام این حرکت در مرحله اول باید راجع به دو مطلب خاص نکاتی را ارائه کنیم و مجدد ادامه دهیم

برای پیاده سازی دستورات Dax از ابزاری به اسم Tabular  Editor استفاده میکنم که میتوانید از مسیر زیر دانلود و نصب کنید.

https://tabulareditor.com/

در آینده سعی خواهم کرد یک مقاله هم در ارتباط با این ابزار ارائه دهم.

نکته مهم در ارتباط با این ابزار این هست که این ابزار پس از نصب در Power Bi Desktop  و در بخش External Tools اضافه میشود و میتوانید از همان جا نرم افزار را باز کنید و مدل داده ای خود را مشاهده و تغییرات را اعمال کنید.

مطلب دوم که پیش نیاز ادامه کار هست  قابلیت جدیدی در زبان  Daxهست به نام Calculation Group که به شما اجازه میدهد گروهی از Measure ها را در قابل یک دسته بندی تعریف کنید و روی آنها مانور دهید

نکته : درSsas Tabular این ویژگی روی Campatiblity Level 1500 قابل استفاده می باشد.

در آینده حتما این مورد را نیز درقالب یک مقاله آموزش خواهیم داد.

خب مجدد ادامه خواهیم داد.

در ادامه با افزودن یک  Calulation Group و ۳ آیتم به آن ۳ Measure  با درصد های مختلف از فروش را به آن اضافه میکنیم.(۲۰% و ۳۰% و ۵۰ %).

VAR PercToUse = 20
VAR prodsAnsSales =
    ADDCOLUMNS (
        ALLSELECTED ( DimPart[LocalizedName] ),
        "@Amt", [FactSpExchangePart Amount]
    )
VAR TotlSales =
    SUMX ( prodsAnsSales, [@Amt] )
VAR prodsAndSalesRT =
    ADDCOLUMNS (
        prodsAnsSales,
        "@AmtRT%",
            VAR CurrentAmt = [@Amt]
            VAR HigherValues =
                FILTER ( prodsAnsSales, [@Amt] >= CurrentAmt )
            VAR Result =
                DIVIDE ( SUMX ( HigherValues, [@Amt] ), TotlSales )
            RETURN
                round(Result*100,2) 
    )
VAR TopPercProducts = FILTER(prodsAndSalesRT,[@AmtRT%] <= PercToUse)
RETURN CALCULATE(
    SELECTEDMEASURE (),
    KEEPFILTERS(TopPercProducts)
)

در بالا ملاحظه میفرمایید که بجای VALUES ( DimPart[LocalizedName] )  از

ALLSELECTED ( DimPart[LocalizedName] ), استفاده کرده ایم چرا که میخواهیم بر فیلتر بر اساس انتخابی که انجام داده ایم اعمال شود و نه تمام محصولات.

نکته بعدی اینکه در انتها متغیری به نام TopPercProducts اضافه شده است که محصولات را بر اساس درصد انتخابی فیلتر کرده است.

با اعمال تغییرات و Deploy شدن آن در Power Bi شاهد اضافه شدن این Measure  به لیست خواهیم شد.

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

حال پس از انتخاب از لیست TopPercent لیست فروش بدین شکل در خواهد آمد.