تغییر رنگ ستون های زیر میانگین در نمودار
در این مقاله با نحوه هایلایت کردن بخش هایی از نمودار که در بازه مورد نظر، کمتر از میانگین بازه قرار گرفته، آشنا خواهیم شد. البته نکته ای که در مورد مثال مورد استفاده در این مقاله وجود داره اینه که محور زمان رو به صورت سلسله مراتبی (Hierarchy) استفاده خواهیم کرد و با هر گامی که به مرتبه بعدی می رویم اثر تغییر رنگ در اون بازه دیده خواهد شد.
چرا از analytics استفاده نمی کنیم؟
احتمالا با analytics نمودار ها آشنا هستید. با استفاده از این قابلیت امکان اضافه کردن مقادیر ثابت (Constant)، کمینه (Min) و بیشینه (Max) و همچنین میانگین (Average) به صورت خط افقی یا عمودی (در نمودار های bar) وجود دارد. اگر خط میانگینی رو از قسمت analytics به نمودار اضافه کنید؛ خروجی مورد نظر مقاله حاصل خواهد شد. اگر چه خروجی به خاطر یکنواخت بودن ظاهر دلنشینی نخواهد داشت و به قولی با نگاه اول تفسیر لازمی رو از نمودار نخواهید داشت.
برای ساخت نمودار این مقاله از نسخه Power Bi October 2020 استفاده کردم و همچنین داده های مورد استفاده رو از لینک زیر می تونید دانلود کنید.
دانلود فایل داده (فقط حاوی ستون های مورد نظر گزارش)
ساخت نمودار
اولین قدم، ساخت یک hierarchy برای محور زمان است که با انتخاب ستون های سال، فصل و ماه از جدول DimDate این کار رو انجام می دهیم . البته می تونید ترتیب ستون ها رو به صورتی در Values نمودار قرار دهید که همون عملکرد hierarchy رو در نهایت داشته باشید به صورت زیر:
ولی پیشنهادم اینه به صورت زیر و در قالب hierarchy وارد کنید:
برای اینکه مرتب سازی تاریخ ها به ترتیب درست تاریخ شمسی صورت بگیره! حتما ستون های فصل و ماه رو که به صورت متنی هستند با استفاده از sort by column براساس مقادیر عددیشون مرتب کنید. به طور مثال: MonthName رو براساس MonthNo مرتب کنید و SeasonName رو براساس SeasonCode. سلسله مراتب ساخته شده در حالت نمایش به این صورت خواهد شد:
رابطه ای بین دو جدول FactInternetSales و DimDate و ستون های DateKey هر دو جدول ایجاد کنید.
برای آن که میانگین هر سطح را مشخص کنیم تا زمانی که به آن سطح با استفاده از expand برویم، مقادیر را با میانگین مقایسه کنیم لازم است ابتدا مشخص کنیم سطح مورد نظر چه بازه ای را پوشش می دهد. به طور مثال “سال” شامل “فصل” و “ماه” می شود ولی “ماه” تنها شامل خودش می شود و “سال” و “ماه” را پوشش نمی دهد. به همین منظور در این قسمت از تابع ISINSCOPE برای تشخیص سطح پوشش استفاده می کنیم.
تصویر بالا خروجی سه measure زیر را نشان می دهد که هر سطح در صورت پوشش سطح زیرین خود True خواهند شد و سطح هایی که سطح بالا را پوشش نمی دهند و شامل نمی شوند False خواهند شد.
ISINSCOPE Year = ISINSCOPE(DimDate[PersianYearInt]) ISINSCOPE Season = ISINSCOPE(DimDate[SeasonName]) ISINSCOPE Month = ISINSCOPE(DimDate[PersianMonthName])
در صورتی که این measure ها رو در قالب یک تابع switch با if بنویسیم، خروجی برچسبی از آن سطوح خواهد شد.
Hierarchy Level =
SWITCH(
TRUE()
, ISINSCOPE(DimDate[PersianMonthName]), "ماه"
, ISINSCOPE(DimDate[SeasonName]), "فصل"
, ISINSCOPE(DimDate[PersianYearInt]), "سال"
)
فقط توجه کنید که در فرمول بالا سطوح پایینتر رو در ابتدا بیاورید تا از سطوح پایین label گذاری صورت گیرد.
در این مرحله که متوجه شدیم در چه سطحی قرار داریم نوبته محاسبه میانگینه!
برای محاسبه میانگین، سه measure زیر را ایجاد کنید و قبل از آن یک measure برای جمع کردن مقادیر فروش ایجاد کنید.
Total = CALCULATE(SUM(FactInternetSales[SalesAmount]))
Monthly Avg =
CALCULATE(
AVERAGEX(
ALL(DimDate[PersianYearInt], DimDate[PersianMonthName])
, [Total]
)
, ALLEXCEPT(DimDate, DimDate[PersianYearInt], DimDate[PersianMonthName])
)
Season Avg =
CALCULATE(
AVERAGEX(
ALL(DimDate[PersianYearInt], DimDate[SeasonName])
, [Total]
)
, ALLEXCEPT(DimDate, DimDate[PersianYearInt], DimDate[SeasonName])
)
Year Avg =
CALCULATE(
AVERAGEX(
ALL(DimDate[PersianYearInt])
, [Total]
)
, ALLEXCEPT(DimDate, DimDate[PersianYearInt])
)
اگر به مقادیر سه measure بالا و نمودار ابتدای مقاله دقت کنید متوجه خواهید شد که دقیقا به میانگین های بازه دست پیدا کردیم. در فرمول های بالا مقادیر هر سطح را به صورتی حساب کردیم که سطح مورد نظر را در حالت فیلتر در نظر گرفتیم. به طور نمونه میانگین ماهیانه رو به این صورت میشه تجزیه و تحلیل کرد :
پس از جمع بندی همه موارد بالا و ترکیب میانگین با تابع switch و تعیین رنگ به سراغ data color میرویم تا شرط رنگ رو اعمال کنیم.
Column Chart Avg Conditional Formatting =
SWITCH(
TRUE()
, ISBLANK([Total]), BLANK()
, [Total] < [Average Sales by Hierarchy Level], "#e76f51"
, BLANK()
)
فرمول بالا در صورتی که مقدار جمع ستون (Total) کوچکتر از میانگین باشه رنگ “e76f51” رو خواهیم داشت. هر چند شما می تونید از عبارت هایی چون “red” یا “orange” و … هم برای تعیین رنگ استفاده کنید.
از قسمت Data colors نمودار measure بالا رو انتخاب کنید و ok کنید.
و نتیجه :
سلام.
جزئیات این مژر را جا انداختید.
[Average Sales by Hierarchy Level]
سلام بله ممنون از موردی که بیان کردید
Average Sales by Hierarchy Level =SWITCH(TRUE()
, ISINSCOPE('Date'[Day]), [Daily Avg]
, ISINSCOPE('Date'[Month]), [Monthly Avg]
, ISINSCOPE('Date'[Quarter]), [Quarterly Avg]
, ISINSCOPE('Date'[Semester]), [Semesterly Avg]
, ISINSCOPE('Date'[Year]), [Yearly Avg]
)