Power BI矩阵视觉仅在总计区域显示指定度量值实现方案

问题场景 #

使用Power BI开展报表可视化开发时，需实现类Matrix（矩阵）布局：以日期为列维度展示度量值SCADA Volume，同时在所有日期列之后的总计区域额外展示若干度量值。直接将附加度量值拖入矩阵「值」字段时，度量值会在所有日期列下重复显示，无法仅在总计区域呈现。

方案选型 #

采用非对称列方案实现需求，核心逻辑是创建计算组（Calculation Group），为矩阵中每一列单独定义计算逻辑，自定义列的展示顺序和计算规则，完全替代原生矩阵的自动列生成和总计逻辑。
由于场景需要动态引用回溯日期，需先在Calendar日期表中新增偏移类计算列，用于定位匹配目标日期。

配置实现 #

1. 构建日期维度表 #

新建Calendar日期表，写入如下DAX代码生成带日期偏移标识的完整日期维度：

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "DateKey", FORMAT([Date], "YYYY") & FORMAT([Date], "MM") & FORMAT([Date], "DD")
    , "Year", YEAR([Date])
    , "Mo", MONTH([Date]) 
    , "day", DAY([Date])
    , "DayName (short)", FORMAT([Date],"ddd") 
    , "DayNum", WEEKDAY([Date]) 
    , "Weeknum", WEEKNUM([Date],1)
    , "Mo Name", FORMAT([Date], "MMM")
    , "Mo-Yr", FORMAT([Date], "MMM") &"-"& FORMAT([Date],"YY")
    , "Mo_startDate", EOMONTH([Date],-1) + 1
    , "Yr_startDate", DATE(YEAR([Date]),1,1)
    , "Qtr", QUARTER([Date])
    , "Qtr Name", "Qtr " & FORMAT([Date], "Q")
    , "Yr Name", "YR " & FORMAT([Date], "YYYY")
    , "Mo_endDate", EOMONTH([Date],0) 
    , "SortDesc_YrMo", - DATEDIFF(date(9999, 12, 31), EOMONTH([Date],-1)+1, MONTH)
    , "SortDesc_Date", - DATEDIFF(date(9999, 12, 31), [Date], DAY)
    , "Date (DESC)", [Date]
    , "Mo-Yr (DESC)",  FORMAT([Date], "MMM") &"-"& FORMAT([Date],"YY")
    , "Days Offset", DATEDIFF(TODAY(),[Date],DAY) 
    , "Months Offset", DATEDIFF(TODAY(),[Date],MONTH) 
    , "Years Offset", DATEDIFF(TODAY(),[Date],YEAR) 
    , "Weeks Offset", DATEDIFF(TODAY(),[Date],WEEK)
    , "Weeks Back Offset", DATEDIFF(TODAY(),[Date],WEEK) * -1
    , "Lookback Day Name", "Wk " & DATEDIFF(TODAY(),[Date],WEEK) * -1 & ": " & FORMAT([Date],"ddd") 
    , "WeekDayOffset_Id", CONVERT( CONVERT(DATEDIFF(TODAY(),[Date],WEEK) * -1, STRING) &  CONVERT(WEEKDAY([Date]), STRING), DOUBLE) 
    , "WeekDayOffset_Sort", CONVERT( CONVERT(DATEDIFF(TODAY(),[Date],WEEK), STRING) &  CONVERT(WEEKDAY([Date]), STRING), DOUBLE) 
)

表中WeekDayOffset_Id、Weeks Back Offset等字段为非对称列匹配日期的核心字段，可根据实际业务的日期回溯规则调整计算逻辑。

2. 配置计算组计算项 #

使用Tabular Editor新建计算组，按展示顺序逐一添加计算项：前序计算项对应当周每日的SCADA Volume值，后序计算项对应总计区域需要展示的周汇总、多周累计、均值、环比等附加度量值，临时校验类计算项返回BLANK()即可自动隐藏，不会在报表中展示。
完整计算项配置JSON如下，可直接导入使用：

{
  "InstanceID": "bae2764b-a0be-4411-82e1-d5c723ec9918",
  "calculationitems": [
    {
  "name": "Wk 1: Sun",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[WeekDayOffset_Id] = 11\n)",
  "ordinal": 8,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "Wk 1: Mon",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[WeekDayOffset_Id] = 12\n)",
  "ordinal": 9,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "Wk 1: Tues",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[WeekDayOffset_Id] = 13\n)",
  "ordinal": 10,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "Wk 1: Wed",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[WeekDayOffset_Id] = 14\n)",
  "ordinal": 11,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "Wk 1: Thu",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[WeekDayOffset_Id] = 15\n)",
  "ordinal": 12,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "Wk 1: Fri",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[WeekDayOffset_Id] = 16\n)",
  "ordinal": 13,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "Wk 1: Sat",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[WeekDayOffset_Id] = 17\n)",
  "ordinal": 14,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "TOTAL Wk1",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[Weeks Back Offset] = 1\n)",
  "ordinal": 15,
  "modifiedTime": "2022-06-08T13:44:54.063333",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "TOTAL: 2 wks",
  "expression": "CALCULATE (\n    SELECTEDMEASURE (),\n    ALL ( 'Calendar' ),\n    'Calendar'[Weeks Back Offset] >= 1\n        && 'Calendar'[Weeks Back Offset] <= 2\n)",
  "ordinal": 16,
  "modifiedTime": "2022-06-08T13:44:04.866667"
},
    {
  "name": "7-day Avg",
  "expression": "VAR Vol_7dRolling =\n    CALCULATE (\n        SELECTEDMEASURE (),\n        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -7, DAY )\n    )\nVAR Avg7d =\n    DIVIDE ( Vol_7dRolling, 7 )\nRETURN\n    Avg7d",
  "ordinal": 17,
  "modifiedTime": "2022-06-08T13:44:04.866667",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "PW Avg",
  "expression": "VAR Vol_PW =\n    CALCULATE (\n        SELECTEDMEASURE (),\n        FILTER ( 'Calendar', 'Calendar'[Weeks Back Offset] = 2 )\n    )\nVAR Avg7dPW =\n    DIVIDE ( Vol_PW, 7 )\nRETURN\n    Avg7dPW",
  "ordinal": 18,
  "modifiedTime": "2022-06-08T21:56:11.783333",
  "formatStringDefinition": {
    "expression": "\"#,0\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "% Δ PW",
  "expression": "VAR Vol_7dRolling =\n    CALCULATE (\n        SELECTEDMEASURE (),\n        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -7, DAY )\n    )\nVAR Avg7d =\n    DIVIDE ( Vol_7dRolling, 7 ) \n// Prior Week\nVAR Vol_PW =\n    CALCULATE (\n        SELECTEDMEASURE (),\n        FILTER ( 'Calendar', 'Calendar'[Weeks Back Offset] = 2 )\n    )\nVAR Avg7dPW =\n    DIVIDE ( Vol_PW, 7 ) \n// Compare \nVAR ChangeDailyAvg = \n    ( Avg7d - Avg7dPW )\nVAR PctChangeDailyAvg = \n    DIVIDE(ChangeDailyAvg, Avg7d )\nRETURN\n    PctChangeDailyAvg",
  "ordinal": 19,
  "modifiedTime": "2022-06-08T22:03:06.576667",
  "formatStringDefinition": {
    "expression": "\"#,0.0%;-#,0.0%;#,0.0%\"",
    "modifiedTime": "2022-06-08T03:33:39.253333"
  }
},
    {
  "name": "* Week Roll",
  "description": "These are temp columns used for validation. BLANK() return value will exclude this calculated item",
  "expression": "VAR Vol_7dRolling =\n    CALCULATE (\n        SELECTEDMEASURE (),\n        DATESINPERIOD ( 'Calendar'[Date], MAX ( 'Calendar'[Date] ), -7, DAY )\n    )\nVAR Avg7d =\n    DIVIDE ( Vol_7dRolling, 7 )\nRETURN\n    // Vol_7dRolling\n    BLANK()",
  "ordinal": 20,
  "modifiedTime": "2022-06-08T22:05:29.33"
},
    {
  "name": "* PW Roll",
  "description": "These are temp columns used for validation. BLANK() return value will exclude this calculated item",
  "expression": "VAR Vol_PW =\n    CALCULATE (\n        SELECTEDMEASURE (),\n        FILTER ( 'Calendar', 'Calendar'[Weeks Back Offset] = 2 )\n    )\nVAR Avg7dPW =\n    DIVIDE ( Vol_PW, 7 )\nRETURN\n    // Vol_PW\n    BLANK()",
  "ordinal": 21,
  "modifiedTime": "2022-06-08T22:05:29.33"
}
  ]
}

3. 矩阵视觉对象配置 #

将计算组的计算项字段拖入矩阵列区域，SCADA Volume度量值拖入值区域，需要展示的维度字段拖入行区域，关闭原生矩阵的总计开关，即可实现日期列后追加自定义总计度量值的效果，附加度量值不会在日期列下重复显示。

内容的提问来源于stack exchange，提问作者SherlockSpreadsheets