כאשר לומדים לראשונה כיצד להשתמש ב- Power Pivot, רוב המשתמשים מגלים שהעוצמה האמיתית היא צבירה או חישוב של תוצאה בדרך כלשהי. אם הנתונים שלך כוללים עמודה עם ערכים מספריים, באפשרותך צבירה בקלות על-ידי בחירתם ב- PivotTable או Power View רשימת שדות. מטבעו, מכיוון שהוא מספרי, הוא ימוסכם באופן אוטומטי, י ממוצע, נספר או כל סוג של צבירה שתבחר. פעולה זו נקראת מידה רמזית. אמצעים מפורשים הם נהדרים עבור צבירה מהירה וקלה, אך יש להם מגבלות, ומגבלות אלה כמעט תמיד יכולות להתגבר על אמצעים מפורשיםועמודות מחושבות.
תחילה נחפש דוגמה שבה אנו משתמשים בפריט עמודה כדי להוסיף ערך טקסט חדש עבור כל שורה בטבלה בשם Product. כל שורה בטבלה Product מכילה כל מיני מידע אודות כל מוצר שאנו מוכרים. יש לנו עמודות עבור שם מוצר, צבע, גודל, מחיר סוחר וכו'. יש לנו טבלה קשורה נוספת בשם קטגוריית מוצר המכילה עמודה ProductCategoryName. מה שאנו רוצים הוא שכל מוצר בטבלה Product יכלול את שם קטגוריית המוצר מהטבלה קטגוריית מוצר. בטבלת המוצרים שלנו, אנו יכולים ליצור עמודה בשם קטגוריית מוצר כך:
הנוסחה החדשה של קטגוריית המוצר משתמשת בפונקציה DAX קשורה כדי לקבל ערכים מ- ProductCategoryName עמודה בטבלה 'קטגוריית מוצר' הקשורה ולאחר מכן מזנת ערכים אלה עבור כל מוצר (כל שורה) בטבלה Product.
זוהי דוגמה נהדרת לא אופן השימוש ב- עמודה כדי להוסיף ערך קבוע עבור כל שורה שנוכל להשתמש בה מאוחר יותר באזור ROWS, COLUMNS או FILTERS של PivotTable או ב- Power View הדוח.
בוא ניצור דוגמה נוספת שבה אנו רוצים לחשב שולי רווח עבור קטגוריות המוצרים שלנו. זהו תרחיש נפוץ, גם בהרבה ערכות לימוד. יש לנו טבלת מכירות במודל הנתונים שלנו הכוללת נתוני טרנזקציות, ויש קשר גומלין בין הטבלה Sales לטבלה 'קטגוריית מוצר'. בטבלה Sales, יש לנו עמודה הכולל סכומי מכירות ועוד עמודה הכולל עלויות.
אנו יכולים ליצור עמודה מחושבת המחשבת סכום רווח עבור כל שורה על-ידי חיסור ערכים ב- COGS עמודה מתוך ערכים ב- SalesAmount עמודה, כך:
כעת, אנו יכולים ליצור PivotTable ולגרור את השדה קטגוריית מוצר אל COLUMNS, ואת השדה החדש רווח לאזור VALUES (עמודה בטבלה ב- PowerPivot הוא שדה ברשימת השדות של PivotTable). התוצאה היא מידה ישירה בשם Sum of Profit. זהו כמות מצטברת של ערכים מהרווח עמודה עבור כל אחת מקטגוריות המוצרים השונות. התוצאה שלנו נראית כך:
במקרה זה, רווח הגיוני רק כשדה ב- VALUES. אם היינו שמים את Profit באזור COLUMNS, ה- PivotTable שלנו היה נראה כך:
שדה הרווח שלנו אינו מספק מידע שימושי כלשהו כאשר הוא ממוקם באזורים COLUMNS, ROWS או FILTERS. זה הגיוני רק כערך מצטבר באזור VALUES.
מה שעשינו הוא ליצור עמודה רווח מחשב שולי רווח עבור כל שורה בטבלה Sales. לאחר מכן הוספנו רווח לאזור VALUES של ה- PivotTable שלנו, ויצרנו באופן אוטומטי מידה שקולה, כאשר תוצאה מחושבת עבור כל אחת מקטגוריות המוצר. אם אתה חושב שבאמת חישבנו רווח עבור קטגוריות המוצרים שלנו פעמיים, אתה צודק. תחילה חישבנו רווח עבור כל שורה בטבלה Sales ולאחר מכן הוספנו רווח לאזור VALUES שבו הוא נצבר עבור כל אחת מקטגוריות המוצר. אם אתה גם חושב שלא באמת היינו צריכים ליצור את ה- Profit calculated עמודה, אתה גם נכון. אך כיצד ניתן לחשב את הרווח שלנו מבלי ליצור רווח מחושב עמודה?
רווח, באמת יחושב טוב יותר כאמצעי מפורש.
כעת, נשאיר את הרווח המחושב עמודה בטבלת המכירות ובקטגוריה 'מוצר' ב- COLUMNS וברווח בערכים של PivotTable שלנו, כדי להשוות את התוצאות שלנו.
באזור החישוב של טבלת המכירות שלנו, ניצור מידה בשם Total Profit (כדי להימנע מהתנגשויות מתן שמות). בסופו של דבר, היא תניב את אותן תוצאות כמו אותן תוצאות אותן תוצאות אותן עשינו בעבר, אך ללא רווח מחושב עמודה.
תחילה, בטבלה Sales, אנו בוחרים את עמודה SalesAmount ולאחר מכן לחץ על סכום אוטומטי כדי ליצור מידה מפורשת של Sum of SalesAmount. זכור, מידה מפורשת היא מידה שאנו יוצרים באזור החישוב של טבלה ב- Power Pivot. אנו עושים זאת גם עבור cogs עמודה. אנו נשנה את השם של סכום SalesAmount ו- Total COGS אלה כדי להקל על זיהוים.
לאחר מכן, אנו יוצרים מידה נוספת באמצעות נוסחה זו:
Total Profit:=[ Total SalesAmount] - [Total COGS]
הערה: אנו יכולים גם לכתוב את הנוסחה שלנו כ- Total Profit:=SUM([SalesAmount]) - SUM([COGS]), אך על-ידי יצירת מדדים נפרדים של Total SalesAmount ו- Total COGS, נוכל להשתמש בהם גם ב- PivotTable שלנו, ואנחנו יכולים להשתמש בהם כארגומנטים בכל מיני נוסחאות מידה אחרות.
לאחר שינוי התבנית החדשה של מדד הרווח הכולל למטבע, נוכל להוסיף אותה ל- PivotTable שלנו.
באפשרותך לראות את מידת הרווח הכולל החדשה שלנו מחזירה את אותן תוצאות כמו יצירת רווח מחושב עמודה מכן הצבתו ב- VALUES. ההבדל הוא שמדד הרווח הכולל שלנו יעיל הרבה יותר והופכת את מודל הנתונים שלנו לנקי ורז יותר מכיוון שאנו מחושבים באותו הזמן ורק עבור השדות שאנו בוחרים עבור PivotTable שלנו. אנחנו לא ממש צריכים ש- Profit יחושב עמודה הכל.
מדוע החלק האחרון חשוב? עמודות מחושבות מוסיפות נתונים למודל הנתונים, והנתונים משתלטים על הזיכרון. אם אנו מרעננים את מודל הנתונים, יש צורך גם לעבד משאבים כדי לחשב מחדש את כל הערכים ב- Profit עמודה. אין צורך באמת לקבל משאבים כמו זה מכיוון שאנחנו באמת רוצים לחשב את הרווח שלנו כאשר אנו בוחרים את השדות שאנחנו רוצים רווח עבורם ב- PivotTable, כמו קטגוריות מוצרים, אזור או לפי תאריכים.
בוא נחפש דוגמה אחרת. אחד שבו ערך מחושב עמודה תוצאות שממבט ראשון נראה נכון, אך...
בדוגמה זו, אנו רוצים לחשב סכומי מכירות כאחוז מסכום המכירות הכולל. אנו יוצרים ערך מחושב עמודה % of Sales בטבלת המכירות שלנו, כך:
מצב הנוסחה שלנו: עבור כל שורה בטבלה Sales, חלק את הסכום ב- SalesAmount עמודה לפי הסכום הכולל של כל הסכומים ב- SalesAmount עמודה.
אם ניצור PivotTable ונוסיף קטגוריית מוצר לעמודות ונבחר את אחוז המכירות החדש עמודה כדי להכניס אותו ל- VALUES, סכום המכירות שלנו יהיה סכום כולל של % עבור כל אחת מקטגוריות המוצרים שלנו.
אוקיי. זה נראה טוב עד כה. אך, בוא להוסיף כלי פריסה. אנו מוסיפים שנה קלנדרית ולאחר מכן בוחרים שנה. במקרה זה, נבחר את 2007. זה מה שאנחנו מקבלים.
במבט ראשון, ייתכן שזה עדיין יופיע נכון. עם זאת, האחוזים שלנו אמורים להיות בסך הכל 100%, מכיוון שאנו רוצים לדעת את אחוז המכירות הכולל עבור כל אחת מקטגוריות המוצר שלנו עבור 2007. אז מה השתבש?
אחוז המכירות עמודה אחוז עבור כל שורה שהיא הערך ב- SalesAmount עמודה מחולק לסכום הכולל של כל הערכים ב- SalesAmount עמודה. ערכים בפריט מחושב עמודה קבועים. הן תוצאה בלתי ניתנת להשתנות עבור כל שורה בטבלה. כאשר הוספנו את % of Sales ל- PivotTable שלנו, הוא נצבר כסכום של כל הערכים ב- SalesAmount עמודה. סכום זה של כל הערכים ב- % of Sales עמודה תמיד יהיה 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. אם נבחר שנה אחרת, או יותר משנה אחת בכלי הפריסה CalendarYear, אנו מקבלים אחוזים חדשים עבור קטגוריות המוצרים שלנו, אך הסכום הכולל שלנו הוא עדיין 100%. אנו יכולים להוסיף כלי פריסה ומסננים אחרים גם כן. אחוז המכירות הכולל שלנו יפיק תמיד אחוז מהמכירות הכוללות ללא קשר לכל כלי הפריסה או המסננים שהוחלו. עם מידות, התוצאה מחושבת תמיד בהתאם להקשר שנקבע על-ידי השדות ב- COLUMNS ו- ROWS, ולפי מסננים או כלי פריסה המוחלים. זוהי עוצמת האמצעים.
להלן כמה קווים מנחים שיעזורו לך כאשר אתה מחליט אם ערך מחושב עמודה או מידה הוא הנכון לצורך חישוב מסוים:
שימוש בעמודות מחושבות
-
אם ברצונך שהנתונים החדשים יופיעו בשורות, בעמודות או במסננים ב- PivotTable, או בציר, במקרא או בפריט חזותי של Power View, עליך להשתמש בפריט עמודה. בדיוק כמו עמודות רגילות של נתונים, ניתן להשתמש בעמודות מחושבות כשדה בכל אזור, ואם הן מספריות, ניתן גם לצבר טורים מחושבים.
-
אם ברצונך שהנתונים החדשים שלך יהיו ערך קבוע עבור השורה. לדוגמה, יש לך טבלת עמודה של תאריכים, ואתה מעוניין עמודה אחרת המכילה רק את מספר החודש. באפשרותך ליצור עמודה מחושב המחשב רק את מספר החודש מהתאריכים בתיבת הדו-עמודה. לדוגמה, =MONTH('Date'[Date]).
-
אם ברצונך להוסיף ערך טקסט עבור כל שורה לטבלה, השתמש בטבלה מחושבת עמודה. לא ניתן צבירה של שדות עם ערכי טקסט ב- VALUES. לדוגמה, =FORMAT('Date'[Date],"mmmm") מספק לנו את שם החודש עבור כל תאריך בתאריך עמודה בטבלה תאריך.
השתמש במידות
-
אם תוצאת החישוב תהיה תמיד תלויה בשדות האחרים שתבחר ב- PivotTable.
-
אם עליך לבצע חישובים מורכבים יותר, כמו לחשב ספירה בהתבסס על מסנן מסוג כלשהו, או לחשב שנה מעל לשנה או שונות, השתמש בשדה מחושב.
-
אם ברצונך לשמור את גודל חוברת העבודה שלך למינימום ולהגדיל את הביצועים שלה, צור כמה שיותר מהחישובים שלך ככל האפשר. במקרים רבים, כל החישובים שלך יכולים להיות מידות, להקטין באופן משמעותי את גודל חוברת העבודה ולהאיץ את זמן הרענון.
זכור, אין כל רע ביצירת עמודות מחושבות כפי עשינו עם עמודה הרווח שלנו ולאחר מכן צבירה ב- PivotTable או ב- הדוח. למעשה, זו דרך טובה וקלה ללמוד עליה וליצור חישובים משלך. ככל שאתה מבין את שתי התכונות החזקות ביותר של Power Pivot, תרצה ליצור את מודל הנתונים היעיל והמדויק ביותר שתוכל. יש לקוות שהלמדת כאן עוזר. ישנם כמה משאבים נהדרים אחרים בחוץ, הזמינים גם הם לעזור לך. להלן רק כמה: הקשר בנוסחאות DAX, צבירות ב- Power Pivot, ובמרכז המשאבים DAX. בנוסף, למרות שהיא מתקדמת מעט יותר, והיא מוכוונת כלפי אנשי מקצוע חשבונאות וכספים, מודל הנתונים 'רווח' ו'ניתוח נתונים' עם Microsoft Power Pivot ב- Excel טעון בדוגמאות נהדרות של מידול נתונים ונוסחאות.
