رتبه بندی از جمله محاسبات پر کاربرد در گزارش هاست. گزارش هایی مثل رتبه بندی فروشندگان یا بدست آوردن پر فروش ترین محصولات. در بین توابع DAX از تابع RANKX به این منظور استفاده میشه. از این تابع برای برگرداندن شماره و رتبه مقادیر مورد نظر در لیستی از اعداد استفاده میشه. قراره در این مقاله به بررسی این تابع بپردازیم و البته چند نکته هم در مورد نحوه استفاده بهتر از این تابع رو با هم بررسی کنیم!
اولین قدم در استفاده از تابع RANKX
الگو یا syntax تابع RANKX :
|
1 |
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]]) |
آرگومان اول table: نام جدول مورد نظر برای محاسبات
آرگومان دوم expression: هر نوع تابع DAX یا measure یا عبارت محاسباتی که یک مقدار یعنی scalar برگردونه
برای محاسبه رتبه فقط همین دو مقدار اول لازم هستند و مابقی آرگومان ها اختیاری هستند که البته جلوتر به مثال های کامل تر از نحوه استفاده از اون ها می پردازیم.
فرض کنید جدولی با داده های زیر داریم که شامل نام محصول و تعداد مبلغ فروش باشه:
در جدول بالا قراره رتبه رو براساس Qty حساب کنیم. در ابتدا روش هایی که به جواب غلط میرسونند ما رو تست می کنیم تا به روش صحیح دست پیدا کنیم!
در صورتی که فرمول رو به صورت زیر بنویسیم با خطایی مواجه میشیم که دقیقا همون شرطیه که در expression توضیح دادیم. یعنی خروجی حتما و حتما لازمه scalar باشه.
|
1 |
rank_0 = RANKX('Table', 'Table'[Qty]) |
البته دقت کنید که اگر همین فرمول رو به عنوان CALCULATED COLUMN اضافه کنیم به درستی محاسبات صورت می گیره چون در جدول سطر به سطر فیلتر داریم.
پس با این توضیحات اگه به آرگومان دوم فقط یک مقدار عددی رو اضافه کنیم خطایی دریافت نمی کنیم (مسلما جواب درستی نیست) به این صورت:
|
1 |
rank_1 = RANKX('Table', 1) |
در نتیجه اگه در این مثال به آرگومان دوم مقدار جمع رو اضافه کنیم که خروجیش فقط یک مقداره و جمع مقدار محصولمون هست پس احتمالا به نتیجه می رسیم! 🤔
|
1 |
rank_2 = RANKX('Table', SUM('Table'[Qty])) |
و اما نشد!
روش صحیح برای MEASURE و اما چرا؟
مشکلی که وجود داره اینه که SUM محاسبات رو به صورت Row Context انجام میده و اگه شما به عنوان expression اون رو در داخل این تابع و measure وارد کنید به نتیجه نمی رسید.
برای این که عملکرد درستی داشته باشه لازمه تبدیلش کنیم به Filter Context. دو روش برای این کار داریم:
روش اول؛ استفاده از CALCULATE که این تبدیل رو انجام میده
|
1 2 3 4 5 6 7 |
rank_3 = RANKX ( ALL ( 'Table' ), CALCULATE ( SUM ( 'Table'[Qty] ) ) ) |
روش دوم؛ استفاده از یک measure دیگه که دقیقا در پس زمینه CALCULATE رو داره انجام میده!
|
1 |
SumQty = SUM('Table'[Qty]) |
|
1 |
rank_4 = RANKX(ALL('Table'), [SumQty]) |
و اما از تابع ALL برای نمایش و برگردوندن لیست محصولات در جدول استفاده می کنیم که البته میتونید از ALLSELECTED هم استفاده کنید. و در اصل با استفاده از ALL فیلترهای اون جدول یا ستون رو که به صورت Filter Context هستند حذف می کنیم. و البته اینکار رو انجام میدیم تا بتونیم مقادیر رو در اون سطح نمایش بدیم. توضیحات بیشتر.
جهت اطلاع: اگه شما از VALUES استفاده کنید به جای ALL یا ALLSELECTED در حقیقت داده ای که بر میگرده جدولی با یک سطر خواهد بود و فقط مقدار 1 رو بر می گردونه.
حذف RANK از قسمت TOTAL
ایردای که در خروجی های بالا وجود داره، بودن عدد 1 در قسمت Total هستش که برای حذف کردنش می تونیم از تابع ISINSCOPE استفاده کنیم. تابع ISINSCOPE ( ‘Table'[Product] ) در صورتی که اون سطح از داده در بازه مورد نظر باشه true بر می گردونه پس چون Total در مجموعه Product نیست پس BLANK خواهد شد.
|
1 2 3 4 5 6 7 8 |
rank_5 = IF ( ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( 'Table' ), [SumQty] ) ) |
و اما آرگومان سوم value
احتمالا اگه از RANKX استفاده کرده باشید پیش اومده که این بخش رو اصلا پر نکنید و به آرگومان های بعدی برید! ولی این آرگومان دقیقا چه کاربردی میتونه داشته باشه؟!
آرگومان سوم value: این مقدار اختیاریه و شامل هر نوع عبارت DAX میشه که مثل قسمت دوم تابع، مقدار scalar برگردونه. اگه خالی گذاشته بشه و تکمیل نشه مقدار از آرگومان قبلی جایگزینش میشه! مثلا رتبه مقدار 50 در مثال 3 بود (تصویر بالا) با وارد کردن مقدار 50 در این قسمت رتبه 50 یعنی 3 برگردونده میشه.
|
1 2 3 4 5 6 7 8 9 |
rank_6 = IF ( ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( 'Table' ), [SumQty], 10 ) ) |
این مقدار با توجه به بازه عددی، کمترین مقدار اون بازه رو بر میگردونه. مثلا برای 65 کوچکترین مقدار به 65 از لیست اعدادی که رتبه بندی کردیم 60 هست و رتبه 60 هم 2 هستش پس خروجی 2 خواهد شد.
برای نشون دادن نمونه ای از کاربرد این آرگومان به مثال بالا جدول زیر رو اضافه کنیم که بازه بندی برای رتبه ها خواهد بود. و البته ستون Rank هم برای نمایش بهتر خروجی اضافه کردم که نیازی نبود.
و الان در بین بازه های بالا که 4 دسته خواهد بود هر مقداری از جدول مثال قبل که در این بازه باشد رتبه اون مقدار از جدول Bins پیدا میشه و بازگردونده میشه.
|
1 2 3 4 5 6 7 8 9 |
rank_7 = IF ( ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( Bins ), Bins[Value], [SumQty], ) ) |
همین مثال رو بر روی ستون Sales Amount هم انجام دهیم خواهیم داشت:
|
1 2 3 4 5 6 7 8 9 |
rank_8 = IF ( ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( BinsAmount ), BinsAmount[Value], CALCULATE(SUM('Table'[Sales Amount])), ) ) |
آرگومان چهارم order
همونطور که از اسمش مشخصه و توضیح لازم نداره این آرگومان ترتیب رو برای رتبه بندی مشخص میکنه.
- مقدار 0 یا False یا DESC: به معنای ترتیب نزولیه یعنی از بزرگترین مقدار به کوچکترین (پیش فرض)
- مقدار 1 یا True یا ASC: به معنای صعودی بودنه که از کوچکترین به بزرگترین خواهد بود
|
1 2 3 4 5 6 7 8 9 |
rank_9 = IF ( ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( 'Table' ), [SumQty], , ASC ) ) |
آرگومان پنجم ties
- Skip (پیش فرض): اگر مقدار تکراری در ارقام باشه اون مقادیر تکراری همه یک رتبه خواهند داشت و رتبه رقم بعدی مجموع رتبه قبلی به علاوه تعداد اون ارقام تکراریه.
|
1 2 3 4 5 6 7 8 9 10 |
rank_10 = IF ( ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( 'Table' ), [SumQty], , ASC , Skip ) ) |
و البته order نزولی هم باشه به همین صورت خواهد بود.
- Dense: در این حالت مقادیر تکراری رتبه یکسانی خواهند داشت و رتبه رقم بعدی بعد از این ارقام تکراری برابر رتبه قبلی به علاوه 1 هستش یعنی دیگه پرشی اتفاق نمیفته و ادامه رتبه های قبلی ادامه پیدا میکنه.
|
1 2 3 4 5 6 7 8 9 10 |
rank_11 = IF ( ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( 'Table' ), [SumQty], , ASC , Dense ) ) |
