Highlighting Below Avg 营业额 per 层级 with 开关() and ISINSCOPE() 达克斯 Functions in Power 双

Highlighting Below Avg 营业额 per 层级 with 开关() and ISINSCOPE() 达克斯 Functions in Power 双

不久前,我在一个项目上工作,客户对柱形图有条件格式要求。
他们希望根据基于您所处的层次结构的平均值,有条件地对图表中的列设置格式。
在这种情况下,我的日历层次结构如下:

  • 日历层次结构:
    • 学期
    • 25美分硬币

我用“Adventure Works DW2017, Internet 营业额”Excel作为Power 双 Desktop中的源。如果我想形象化“Total 营业额” over the above “Calendar Hierarchy”我得到这样的东西:

Line Chart in Power 双 , Total 营业额 by 年

现在我激活“Average Line” from “Analytics”折线图标签。

Adding 平均线 to Line Chart in Power 双

When I drill down in the line chart the 平均线 shows the average of that particular hierarchy level that I am in. This is quite cool that I get the average base on the level that I’m in code free.

Power 双 ,折线图中的钻井

容易吧?

现在,要求是在一个“柱形图”(是的!用柱形图可视化时间序列,’s what the customer wants) and highlight the columns with values below average amount in 橙子 and leave the rest in default theme colour.

因此,我需要创建度量以有条件地格式化柱形图。我还需要在以下方面增加一些智能:

  • 检测我所在的层次结构级别
  • 计算该特定层次结构级别的平均销售额
  • 更改低于平均值的列的颜色

让’s get it done!

Detecting 层级 with ISINSCOPE() 达克斯 Function

微软介绍 ISINSCOPE() 达克斯功能 2018年11月版本 Power 双 桌面。宣布后不久“Kasper de Jonge” wrote a concise 博客文章 关于它。

因此,我尝试使其尽可能简单。这是工作原理,ISINSCOPE()函数返回“True”当指定的列在层次结构级别中时。如前所述,我们有一个“Calendar Hierarchy”包括以下5个级别:

  • 学期
  • 25美分硬币

因此,要确定我们是否处于上述每个层次结构级别中,我们只需要创建如下的DAX度量即可:

ISINSCOPE 年		=	ISINSCOPE('Date'[Year])
ISINSCOPE 学期	=	ISINSCOPE('Date'[Semester])
ISINSCOPE 25美分硬币	=	ISINSCOPE('Date'[Quarter])
ISINSCOPE 月		=	ISINSCOPE('Date'[Month])
ISINSCOPE 天		=	ISINSCOPE('Date'[Day])

现在让’做一个简单的实验。

  • 放a Matrix on the canvas
  • 放the “Calendar Hierarchy” to “Rows”
  • 采取上述措施“Values”
Detecting 年, 学期, 25美分硬币, 月 and 天 hierarchy levels with ISINSCOPE in Power 双 桌面

如你所见“ISINSCOPE 年” shows “True” for the “Year” level. 让’扩展到下一个级别,并查看其他度量如何工作:

层级s in Power 双 桌面

一站式整合措施

现在我们了解了ISINSCOPE()函数的工作原理,让’进一步采取措施,看看如何将所有措施整合为一项措施。请记住,我们的方案是计算每个层次结构级别的平均值。我结合使用“开关()“, “真正()” and “ISINSCOPE()”识别每个级别的功能。我要说明的三个功能的结合使用是一个警告。

这是我们在本节中要实现的目标。我们希望能够在Matrix视觉中显示层次结构级别。为此,我们使用“SWITCH()” function as below:

  • If hierarchy level is 年 then show “Year”
  • 如果层次级别为学期,则显示“Semester”
  • 如果层次结构级别为“四分之一”,则显示“Quarter”
  • 如果层次结构级别为“月”,则显示“Month”
  • If hierarchy level is 天 then show “Day”

让’在DAX中复制以上内容。这样的事情可能行得通吗?

层级 = 
SWITCH(
    真正()
        , ISINSCOPE('Date'[Day]), "天"
        , ISINSCOPE('Date'[Month]), "月"
        , ISINSCOPE('Date'[Quarter]), "25美分硬币"
        , ISINSCOPE('Date'[Semester]), "学期"
        , ISINSCOPE('Date'[Year]), "年"
        , "Other"
    )

根据文档“开关()”函数上面的表达式必须像这样工作:

评估逻辑“TRUE()”针对ISINSCOPE()函数的值列表并返回 多个结果表达式。因此,当我们在带有“Calendar Hierarchy” we’一次即可检测到每个层次结构级别。

Detecting 层级 with 开关, 真正 and ISINSCOPE Functions in 达克斯

如您所见,我们仅用一个度量即可正确检测到层次结构级别。需要注意的是,我们必须按照在层次结构中看到的相反顺序创建值列表。所以,“Day” in “Calendar Hierarchy” is level 5 and “Year”是第1级,因此,我们从“Day”当我们编写SWITCH()函数时。如果我们想用IF()来写上述量度,我们’会像下面这样:

层级 with IF = 
IF(ISINSCOPE('Date'[Day]), "天"
    , IF(ISINSCOPE('Date'[Month]), "月"
        , IF(ISINSCOPE('Date'[Quarter]), "25美分硬币"
            , IF(ISINSCOPE('Date'[Semester]), "学期"
                , IF(ISINSCOPE('Date'[Year]), "年", "Other")
            )
        )
    )
)
Detecting 层级 with IF and ISINSCOPE Functions in 达克斯

Calculate Average of 营业额 层级s

The next step is to calculate Average 营业额 for each hierarchy level as below:

Daily Avg = 
AVERAGEX(
    ALL('Date'[Date])
    , [Total 营业额]
    )
月ly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year], 'Date'[Month], 'Date'[MonthNumberOfYear])
        , [Total 营业额]
        )
    , ALLEXCEPT('Date', 'Date'[Year], 'Date'[Month], 'Date'[MonthNumberOfYear])
    )

请注意,我曾经‘日期'[月]以及‘ALL和ALLEXCEPT函数中的Date'[MonthNumberOfYear]。原因是我排序了‘日期'[月]列‘日期” [[MonthNumberOfYear]。详细了解按另一列对列进行排序的潜在副作用 这里.

25美分硬币ly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year], 'Date'[Quarter])
        , [Total 营业额]
        )
    , ALLEXCEPT('Date', 'Date'[Year], 'Date'[Quarter])
    )
学期ly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year], 'Date'[Semester])
        , [Total 营业额]
        )
    , ALLEXCEPT('Date', 'Date'[Year], 'Date'[Semester])
    )
年ly Avg = 
CALCULATE(
    AVERAGEX(
        ALL('Date'[Year])
        , [Total 营业额]
        )
    , ALLEXCEPT('Date', 'Date'[Year])
    )
层级s and Average of 层级s with 达克斯 in Power 双 桌面

现在,我们需要创建另一种方法,例如“Hierarchy Level”我们之前使用SWITCH(),TRUE()和ISINSCOPE()函数创建的测量“Sales Average”对于每个相关的层次结构级别。该度量如下所示:

Average 营业额 by 层级 = 
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]
    )
营业额 Average per Hierarchy with 达克斯 in Power 双 桌面

Creating Conditional 格式ting Measure

难题的最后一步是制定一项措施,’将用于有条件地设置柱形图的格式。以下措施确定是否“Sales” is below “Average 营业额 by 层级” then returns “Orange”否则它什么都不做。

柱形图 Avg Conditional 格式ting = 
SWITCH(
    真正()
    , ISBLANK([Total 营业额]), BLANK()
    , [Total 营业额] < [Average 营业额 by 层级], "橙子"
    , BLANK()
)

现在我们’重新设置。剩下的唯一部分是使用上述措施来有条件地格式化显示“Sales” Over “Calendar Hierarchy”.

  • 在报告页面上放置柱形图
  • 放“Total 营业额” to “Values”
  • 放“Calendar Hierarchy” to Axis
Showing Total 营业额 by 日历层次结构 with 条形图 in Power 双 桌面
  • 扩大“Data colour” from “Format” tab from “Visualisations” Pane
  • 将鼠标悬停在默认颜色上
  • 请点击ellipsis button
  • 请点击“Conditional 格式ting”
  • 选择“Field Value” from “Format by” dropdown
  • 选择我们从“Based on field”部分,然后单击确定
使用Power 双  Desktop中的措施进行条件格式条形图

这是您得到的:

Highlighting 营业额 below average in hierarchies in Power 双 桌面

As you can see we determined 营业额 below average based on hierarchy level we are at. To make this even better we can enable an average line in the bar chart. This can be done from the “Analytics” tab and enabling “Average line”.

在Power 双  Desktop中启用条形图中的平均折线

Now if you expand down to the other levels you can quickly see the when you have 营业额 below average.

Calculating 营业额 below average with conditional formatting in 条形图 in Power 双 桌面

注意: 条形图的条件格式中使用的上述措施’如果启用,就可以“Drill down”因为它会将过滤器放在您向下钻取的所选项目上。那么你’d最好禁用“Drill down” button from the “Visual Header” settings.

关闭"Drill down"

注意: 仅在将报告发布到Power 双 Service时影响读者视图,因此,您在Power 双 Desktop中看不到它的效果。

Highlighting Below Avg 营业额 per 层级 with 开关() and ISINSCOPE() 达克斯 Functions in Power 双

您以前使用过这种方法吗?您知道解决这个问题的更好方法吗?请在下面的评论部分告诉我们。

One thought on “Highlighting Below Avg 营业额 per 层级 with 开关() and ISINSCOPE() 达克斯 Functions in Power 双

有什么想法吗?在这里与我们分享:

该网站使用Akismet减少垃圾邮件。 了解如何处理您的评论数据.