عندما يتعلم معظم المستخدمين كيفية استخدام Power Pivot للمرة الأولى، يكتشف أن القوة الحقيقية تكمن في تجميع نتيجة أو حسابها بطريقة ما. إذا تضمنت بياناتك عمودا يتضمن قيما رقمية، فيمكنك بسهولة تجميعها من خلال تحديدها في قائمة حقول PivotTable أو Power View. بطبيعته، ولأنه رقمي، سيتم جمعه أو حساب متوسطه أو عده تلقائيا أو أي نوع من أنواع التجميع تحدده. يعرف هذا بأنه مقياس ضمني. تعد المقاييس الضمنية رائعة للتجميع السهل والسريع، ولكن لها حدود، ويمكن التغلب على هذه الحدود دائما باستخدام المقاييسالصريحة والأعمدة المحسوبة.
لنلق أولا نظرة على مثال نستخدم فيه عمودا محسوبا لإضافة قيمة نصية جديدة لكل صف في جدول مسمى "المنتج". يحتوي كل صف في جدول المنتجات على جميع أنواع المعلومات حول كل منتج نبيعه. لدينا أعمدة لاسم المنتج واللون والحجم وسعر التاجر وما إلى ذلك. لدينا جدول آخر مرتبط يسمى "فئة المنتج" يحتوي على العمود ProductCategoryName. ما نريد هو أن يتضمن كل منتج في جدول المنتج اسم فئة المنتج من جدول فئة المنتج. في جدول المنتج الخاص بنا، يمكننا إنشاء عمود محسوب باسم "فئة المنتج" كما يلي:
تستخدم صيغة فئة المنتج الجديدة الدالة RELATED DAX للحصول على قيم من العمود ProductCategoryName في جدول فئة المنتج ذي الصلة ثم تقوم بإدخال هذه القيم لكل منتج (كل صف) في جدول المنتج.
يشكل هذا الخيار مثالا رائعا على كيفية استخدام عمود محسوب لإضافة قيمة ثابتة لكل صف يمكننا استخدامها لاحقا في منطقة "الصفوف" أو "الأعمدة" أو "عوامل التصفية" في PivotTable أو في تقرير Power View.
لننشئ مثالا آخر حيث نريد حساب هامش الربح لفئات منتجاتنا. هذا سيناريو شائع ، حتى في العديد من البرامج التعليمية. لدينا جدول مبيعات في نموذج البيانات يحتوي على بيانات المعاملات، وهناك علاقة بين جدول المبيعات وجدول فئة المنتج. في جدول "المبيعات"، لدينا عمود يحتوي على مبالغ المبيعات وعمود آخر يتضمن تكاليف.
يمكننا إنشاء عمود محسوب يحسب مبلغ الربح لكل صف عن طريق طرح القيم الموجودة في العمود COGS من القيم الموجودة في عمود SalesAmount ، كما يلي:
الآن، يمكننا إنشاء PivotTable وسحب حقل فئة المنتج إلى COLUMNS، وحقل الربح الجديد إلى منطقة VALUES (العمود في جدول في PowerPivot هو حقل في قائمة حقول PivotTable). تكون النتيجة مقياسا ضمنيا يسمى "مجموع الربح". وهو عبارة عن مقدار مجمع من القيم من عمود الأرباح لكل فئة من فئات المنتجات المختلفة. تبدو النتيجة مماثلة لما يلي:
في هذه الحالة، يكون Profit منطقيا فقط كحقل في VALUES. إذا أردنا وضع الربح في منطقة الأعمدة، فسيبدو PivotTable الخاص بنا كما يلي:
لا يوفر حقل الربح الخاص بنا أي معلومات مفيدة عند وضعه في مناطق الأعمدة أو الصفوف أو عوامل التصفية. فهي منطقية فقط كقيمة مجمعة في منطقة القيم.
ما فعلناه هو إنشاء عمود يسمى "الربح" يحسب هامش الربح لكل صف في جدول "المبيعات". ثم أضفنا Profit إلى منطقة VALUES في PivotTable، وأنشأنا مقياسا ضمنيا تلقائيا، حيث يتم حساب نتيجة لكل فئة من فئات المنتجات. إذا كنت تعتقد أننا قمنا بالفعل بحساب الربح لفئات منتجاتنا مرتين ، فأنت على صواب. قمنا أولا بحساب الربح لكل صف في جدول المبيعات، ثم أضفنا الربح إلى منطقة القيم حيث تم تجميعها لكل فئة من فئات المنتجات. إذا كنت تعتقد أيضا أننا لم نحتاج حقا إلى إنشاء عمود الربح المحسوب، فأنت محق أيضا. ولكن ، كيف إذن نحسب أرباحنا دون إنشاء عمود ربح محسوب؟
الربح ، سيكون من الأفضل حقا حسابه كمقياس صريح.
في الوقت الحالي، سنترك عمود الربح المحسوب في جدول المبيعات وفئة المنتج في الأعمدة والربح في قيم PivotTable، لمقارنة نتائجنا.
في منطقة الحساب في جدول المبيعات، سنقوم بإنشاء مقياس يسمى إجمالي الربح (لتجنب تعارضات الأسماء). في النهاية ، سينتج عنه نفس النتائج كما فعلنا من قبل ، ولكن بدون عمود محسوب للربح.
سنحدد أولا في جدول المبيعات العمود SalesAmount ثم انقر فوق "جمع تلقائي" لإنشاء مقياس واضح لمجموع SalesAmount . تذكر أن المقياس الصريح هو المقياس الذي نقوم بإنشائه في منطقة الحساب لجدول في Power Pivot. نحن نفعل الشيء نفسه مع عمود COGS. سنعيد تسمية إجمالي مبلغ المبيعات وإجمالي تكلفة البضائع المباعة هذه لتسهيل التعرف عليها.
ثم نقوم بإنشاء مقياس آخر بهذه الصيغة:
إجمالي الربح:=[إجمالي قيمة المبيعات] - [إجمالي تكلفة البضائع المباعة]
ملاحظة
يمكننا أيضا كتابة الصيغة الخاصة بنا على أنها إجمالي الربح:=SUM([SalesAmount]) - SUM([COGS])، ولكن من خلال إنشاء مقاييس منفصلة لإجمالي SalesAmount وTotal COGS، يمكننا استخدامها في PivotTable أيضا، ويمكننا استخدامها كوسيطات في جميع أنواع صيغ القياس الأخرى.
بعد تغيير تنسيق مقياس إجمالي الربح الجديد إلى عملة، يمكننا إضافتها إلى PivotTable.
يمكنك أن ترى إن مقياس إجمالي الربح الجديد يرجع النتائج نفسها التي يقوم بها إنشاء عمود محسوب "الربح" ثم وضعه في VALUES. الفرق هو أن مقياس إجمالي الربح الخاص بنا أكثر فعالية بكثير ويجعل نموذج البيانات الخاص بنا أنظف وأصغر حجما لأننا نقوم بالحساب في الوقت وفقط للحقول التي نحددها لجدول PivotTable الخاص بنا. لا نحتاج حقا إلى عمود الربح المحسوب بعد كل شيء.
لماذا هذا الجزء الأخير مهم؟ تضيف الأعمدة المحسوبة بيانات إلى نموذج البيانات، وتشغل البيانات الذاكرة. إذا قمنا بتحديث نموذج البيانات، ستكون هناك حاجة أيضا إلى موارد المعالجة لإعادة حساب جميع القيم في عمود الربح. لا نحتاج حقا إلى استخدام موارد مثل هذه لأننا نريد فعلا حساب أرباحنا عند تحديد الحقول التي نريد ربحا منها في PivotTable، مثل فئات المنتجات أو المنطقة أو حسب التواريخ.
لنلق نظرة على مثال آخر. عمود ينشئ فيه عمود محسوب نتائج تبدو للوهلة الأولى صحيحة، ولكن....
في هذا المثال، نرغب في حساب مبالغ المبيعات كنسبة مئوية من إجمالي المبيعات. نقوم بإنشاء عمود محسوب باسم ٪ من المبيعات في جدول المبيعات، كما يلي:
تنص الصيغة على ما يلي: لكل صف في جدول "المبيعات"، قم بقسمة المبلغ الموجود في العمود SalesAmount على إجمالي SUM لكل المبالغ في عمود SalesAmount.
إذا أنشأنا PivotTable وأضفنا فئة المنتج إلى COLUMNS وحددنا عمود "النسبة المئوية للمبيعات " الجديد لدينا لوضعه في VALUES، فسنحصل على إجمالي النسبة المئوية للمبيعات لكل فئة من فئات منتجاتنا.
حسنا. هذا يبدو جيدا حتى الآن. لكن دعونا نضيف مقسم طريقة عرض. نضيف Calendar Year ثم نحدد سنة. في هذه الحالة، سنحدد 2007. هذا ما نحصل عليه.
للوهلة الأولى، قد لا يزال هذا يبدو صحيحا. ولكن ، يجب أن نسب المئوية لدينا يجب أن تبلغ 100٪ ، لأننا نريد معرفة النسبة المئوية لإجمالي المبيعات لكل فئة من فئات منتجاتنا لعام 2007. إذن ما الخطأ الذي حدث؟
قام عمود "النسبة المئوية للمبيعات" بحساب نسبة مئوية لكل صف تمثل القيمة الموجودة في العمود SalesAmount مقسومة على إجمالي مجموع كل القيم في العمود SalesAmount. تكون القيم في العمود المحسوب ثابتة. إنها نتيجة ثابتة لكل صف في الجدول. عندما أضفنا ٪ من المبيعات إلى PivotTable، تم تجميعها كمجموع كل القيم في العمود SalesAmount. سيكون دائما مجموع كل القيم في عمود "٪ من المبيعات" 100٪.
تلميح
تأكد من قراءة السياق في صيغ DAX. فهو يوفر فهما جيدا للسياق على مستوى الصف وسياق عامل التصفية، وهو ما وصفناه هنا.
يمكننا حذف عمود النسبة المئوية للمبيعات المحسوب لأنه لن يساعدنا. بدلا من ذلك، سنقوم بإنشاء مقياس يحسب بشكل صحيح النسبة المئوية لإجمالي المبيعات، بغض النظر عن عوامل التصفية أو مقسمات طرق العرض المطبقة.
هل تتذكر مقياس TotalSalesAmount الذي أنشأناه سابقا، والذي يجمع ببساطة عمود SalesAmount؟ استخدمناها كوسيطة في مقياس إجمالي الربح، وسنستخدمها مرة أخرى كوسيطة في الحقل المحسوب الجديد.
تلميح
لا يعد إنشاء مقاييس صريحة مثل Total SalesAmount وTotal COGS مفيدا في PivotTable أو تقرير فحسب، ولكنه مفيد أيضا كوسيطات في مقاييس أخرى عندما تحتاج إلى النتيجة كوسيطة. هذا يجعل الصيغ أكثر فعالية ويسهل قراءتها. هذه ممارسة جيدة لنمذجة البيانات.
نقوم بإنشاء مقياس جديد بالصيغة التالية:
٪ of total Sales:=([Total SalesAmount]) / CALCULATE([Total SalesAmount], ALLSELECTED())
تنص هذه الصيغة على: قسمة النتيجة من إجمالي SalesAmount على مجموع إجمالي SalesAmount بدون أي عوامل تصفية صفوف أو أعمدة غير تلك المعرفة في PivotTable.
تلميح
تأكد من قراءة المزيد حول الدالتين CALCULATEوALLSELECTED في مرجع DAX.
الآن، إذا أضفنا النسبة المئوية الجديدة من إجمالي المبيعات إلى PivotTable، فسنحصل على:
هذا يبدو أفضل. يتم الآن حساب النسبة المئوية لإجمالي المبيعات لكل فئة منتج كنسبة مئوية من إجمالي المبيعات لعام 2007. إذا حددنا سنة مختلفة أو أكثر من سنة واحدة في مقسم طريقة عرض سنة التقويم، فسنحصل على نسب مئوية جديدة لفئات منتجاتنا، ولكن المجموع الكلي لا يزال 100٪. يمكننا إضافة مقسمات طرق عرض وعوامل تصفية أخرى أيضا. سوف ينتج عن مقياس النسبة المئوية لإجمالي المبيعات دائما نسبة مئوية من إجمالي المبيعات بغض النظر عن مقسمات طرق العرض أو عوامل التصفية المطبقة. باستخدام القياسات، يتم دائما حساب النتيجة وفقا للسياق الذي تحدده الحقول في COLUMNS وROWS، وأي عوامل تصفية أو مقسمات طرق عرض تم تطبيقها. هذه هي قوة التدابير.
فيما يلي بعض الإرشادات لمساعدتك عند تحديد ما إذا كان العمود المحسوب أو القياس مناسبا لاحتياجات حسابية معينة أم لا:
استخدام الأعمدة المحسوبة
- إذا كنت تريد أن تظهر بياناتك الجديدة على الصفوف أو الأعمدة أو في عوامل التصفية في PivotTable أو على محور أو وسيلة إيضاح أو TILE BY في مرئيات Power View، فيجب عليك استخدام عمود محسوب. يمكن استخدام الأعمدة المحسوبة كحقل في أي ناحية، تماما مثل أعمدة البيانات العادية، وإذا كانت رقمية يمكن تجميعها في VALUES أيضا.
- إذا كنت تريد أن تكون بياناتك الجديدة قيمة ثابتة للصف. على سبيل المثال، لديك جدول تواريخ وعمود تواريخ، وتريد عمودا آخر يحتوي على رقم الشهر فقط. يمكنك إنشاء عمود محسوب يحسب رقم الشهر فقط من التواريخ الموجودة في عمود التاريخ. على سبيل المثال، =MONTH('Date'[Date]).
- إذا كنت تريد إضافة قيمة نصية لكل صف إلى جدول، فاستخدم عمودا محسوبا. لا يمكن أبدا تجميع الحقول ذات القيم النصية في VALUES. على سبيل المثال، توفر الصيغة =FORMAT('Date'[Date],"mmmm") اسم الشهر لكل تاريخ في عمود "التاريخ" في جدول "التاريخ".
مقاييس الاستخدام
- إذا كانت نتيجة العملية الحسابية ستعتمد دائما على الحقول الأخرى التي تحددها في PivotTable.
- إذا كنت بحاجة إلى إجراء عمليات حسابية أكثر تعقيدا، مثل حساب عدد استنادا إلى عامل تصفية من نوع ما، أو حساب أساس سنوي أو تباين، فاستخدم حقل محسوب.
- إذا أردت تقليل حجم المصنف إلى الحد الأدنى وزيادة أداؤه إلى أقصى حد، فيمكنك إنشاء أكبر عدد ممكن من العمليات الحسابية قدر الإمكان. وفي حالات عديدة، يمكن أن تكون كل العمليات الحسابية التي تجريها عبارة عن مقاييس، مما يؤدي إلى تقليل حجم المصنف بشكل كبير وتسريع وقت التحديث.
ضع في اعتبارك أنه لا حرج في إنشاء أعمدة محسوبة كما فعلنا مع عمود الربح، ثم تجميعه في PivotTable أو تقرير. إنها في الواقع طريقة جيدة وسهلة حقا للتعرف على وإنشاء العمليات الحسابية الخاصة بك. مع زيادة فهمك لهاتين الميزتين الفعالتين للغاية في Power Pivot، ستحتاج إلى إنشاء نموذج البيانات الأكثر كفاءة ودقة الذي يمكنك إنشاؤه. نأمل أن يكون ما تعلمته هنا مفيدا. هناك بعض الموارد الرائعة الأخرى التي يمكن أن تساعدك أيضا. إليك بعض هذه الأمور: السياق في صيغ DAXوالتجميعات في Power PivotوDAX Resource Center. وعلى الرغم من أن نموذج نمذجة بيانات الربح والخسائر وتحليلها باستخدام Microsoft Power Pivot في Excel يتضمن نماذج بيانات رائعة ونمذجة بيانات في Excel ويتضمن أمثلة مفيدة لإنشاء نماذج بيانات وصيغ.