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