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