同期对比常规状态下可以使用簇状条形图/柱形图,以下是Power BI内置图表的效果。
这个图表的缺陷是,无法反映增长率。以下是使用DAX自定义图表,优化后的效果:
气泡的面积大小代表增长率的大小,颜色区分增长还是下降。图表的度量值如下,把度量值中的今年、同期、增长率替换为你模型中的值,并把度量值放入HTML Content视觉对象即可正常出图,图表可与外部切片器正常联动。度量值的结构见注释。
同期对比组合图 =
VAR Height_Rect = 12 //每个条形高度,12像素
VAR Height_Item = 2 * Height_Rect + 5 //两年对比,每个维度数据间隔5个像素
VAR Width_ItemLabel = 60 //类别标签宽度
VAR Width_Rect = 100 //条形最大长度
VAR FontSize = 6 //字号
VAR Circle_R = Height_Rect //圆圈的最大半径
VAR Space_Btw_Rect_Circle = Width_Rect * 0.3 + Circle_R
VAR ItemCount =
DISTINCTCOUNT ( '店铺信息'[店铺名称] )
VAR MaxValue_ThisYear =
MAXX ( VALUES ( '店铺信息'[店铺名称] ), [业绩_今年] )
VAR MaxValue_LastYear =
MAXX ( VALUES ( '店铺信息'[店铺名称] ), [业绩_同期] )
VAR MaxValue =
MAX ( MaxValue_ThisYear, MaxValue_LastYear )
VAR MaxGrowth =
MAXX ( VALUES ( '店铺信息'[店铺名称] ), ABS ( [增长率] ) )
VAR BarTable =
ADDCOLUMNS (
SUMMARIZE (
'店铺信息',
'店铺信息'[店铺名称],
"Index", RANKX ( ALLSELECTED ( '店铺信息' ), [业绩_今年] )
),
"Store",
//画类别标签 公众号:wujunmin
"<text x='" & Width_ItemLabel - 1 & "' y='" & ( [Index] - 1 ) * Height_Item + Height_Rect & "' text-anchor='end' dominant-baseline='middle' font-weight='bold' font-size='" & FontSize & "' >" & [店铺名称] & "</text>",
"Rect_Background",
//画背景框
"<rect x='" & Width_ItemLabel & "' y='" & ( [Index] - 1 ) * Height_Item + + Height_Rect / 2 & "' width='" & Width_Rect + Space_Btw_Rect_Circle & "' height='" & Height_Rect & "' fill='none' stroke='LightGrey' stroke-width='0.5'/>",
"Rect",
//画本期条形
"<rect x='" & Width_ItemLabel & "' y='" & ( [Index] - 1 ) * Height_Item & "' width='" & Width_Rect * [业绩_今年] / MaxValue & "' height='" & Height_Rect & "' fill='DarkCyan'/>",
"Rect_L",
//画同期条形
"<rect x='" & Width_ItemLabel & "' y='" & ( [Index] - 1 ) * Height_Item + Height_Rect & "' width='" & Width_Rect * [业绩_同期] / MaxValue & "' height='" & Height_Rect & "' fill='LightGrey'/>",
"Text",
//画本期数据标签
"<text x='" & Width_ItemLabel & "' y='" & ( [Index] - 1 ) * Height_Item + + Height_Rect / 2 & "' text-anchor='start' dominant-baseline='middle' font-size='" & FontSize & "' >"
& FORMAT ( [业绩_今年] , "#,##" ) & "</text>",
"Text_L",
//画同期数据标签
"<text x='" & Width_ItemLabel & "' y='" & ( [Index] - 1 ) * Height_Item + + Height_Rect + Height_Rect / 2 & "' text-anchor='start' dominant-baseline='middle' font-size='" & FontSize & "' >"
& FORMAT ( [业绩_同期] , "#,##" ) & "</text>",
"Circle",
//画气泡
"<circle cx='" & Width_ItemLabel + Width_Rect + Space_Btw_Rect_Circle & "' cy='" & ( [Index] - 1 ) * Height_Item + Height_Rect & "' r='"
& Circle_R
* SQRT ( ABS ( [增长率] ) / MaxGrowth ) & "' fill='"
& IF ( [增长率] >= 0, "LightSeaGreen", "Tomato" ) & "' fill-opacity='1'/>",
"Percent",
//画增长率数据标签 公众号:wujunmin
"<text x='" & Width_ItemLabel + Width_Rect + Space_Btw_Rect_Circle & "' y='" & ( [Index] - 1 ) * Height_Item + Height_Rect & "' text-anchor='middle' dominant-baseline='middle' font-size='" & FontSize & "' >"
& FORMAT ( [增长率], "0%" ) & "</text>"
)
VAR Bar =
CONCATENATEX (
BarTable,
[Store] & [Rect_Background] & [Rect] & [Rect_L] & [Text] & [Text_L] & [Circle] & [Percent]
)
VAR SVG = "<svg xmlns='http://www.w3.org/2000/svg' viewbox='0 0 " & Width_ItemLabel + Width_Rect + Space_Btw_Rect_Circle + Circle_R & " " & Height_Item * ItemCount & "'>" & Bar & "</svg>"
RETURN
SVG