# BI技巧丨对比分析 - 知乎

2020-12-07 07:36:39

BOSS：咦？我要干啥来着？哦，对了，白茶？！

BOSS：咳咳，是这样的，就是我想知道，咱们这些个门店最直观的对比情况！就是那种可以选择任意两个门店进行各项数据对比的！

BOSS：我不懂这个那个的，你就告诉我能不能整？

``````分店表B =
DISTINCT ( '分店表' )``````

1

2

3

...

``````FACT_01_销售数量A =
SUM ( '销售明细'[销售数量] )``````

``````FACT_01_销售数量B =
CALCULATE (
[FACT_01_销售数量A],
ALL ( '分店表'[分店名] ),
USERELATIONSHIP ( '销售明细'[所在分店], '分店表B'[分店名] )
)``````

``````FACT_02_成交订单A =
COUNTROWS ( VALUES ( '销售明细'[销售流水] ) )``````

``````FACT_02_成交订单B =
CALCULATE (
[FACT_02_成交订单A],
ALL ( '分店表'[分店名] ),
USERELATIONSHIP ( '销售明细'[所在分店], '分店表B'[分店名] )
)``````

``````FACT_03_顾客数量A =
COUNTROWS ( VALUES ( '销售明细'[客户流水号] ) )``````

``````FACT_03_顾客数量B =
CALCULATE (
[FACT_03_顾客数量A],
ALL ( '分店表'[分店名] ),
USERELATIONSHIP ( '销售明细'[所在分店], '分店表B'[分店名] )
)``````

``````FACT_04_销售金额A =
SUMX ( '销售明细', [FACT_01_销售数量A] * RELATED ( '产品表'[销售价] ) )``````

``````FACT_04_销售金额B =
CALCULATE (
[FACT_04_销售金额A],
ALL ( '分店表'[分店名] ),
USERELATIONSHIP ( '销售明细'[所在分店], '分店表B'[分店名] )
)``````

``````FACT_05_销售成本A =
SUMX ( '销售明细', [FACT_01_销售数量A] * RELATED ( '产品表'[成本] ) )``````

``````FACT_05_销售成本B =
CALCULATE (
[FACT_05_销售成本A],
ALL ( '分店表'[分店名] ),
USERELATIONSHIP ( '销售明细'[所在分店], '分店表B'[分店名] )
)``````

``````FACT_06_销售利润A =
[FACT_04_销售金额A] - [FACT_05_销售成本A]``````

``````FACT_06_销售利润B =
[FACT_04_销售金额B] - [FACT_05_销售成本B]``````

KPIA代码：

``````KPIA =
VAR T1 =
SELECTEDVALUE ( 'Measure'[INDEX] )
VAR T2 =
SWITCH (
TRUE (),
T1 = 01, SELECTEDVALUE ( '分店表'[分店名] ),
T1 = 02, [FACT_01_销售数量A],
T1 = 03, [FACT_02_成交订单A],
T1 = 04, [FACT_03_顾客数量A],
T1 = 05, [FACT_04_销售金额A],
T1 = 06, [FACT_05_销售成本A],
T1 = 07, [FACT_06_销售利润A]
)
RETURN
T2``````

KPIB代码：

``````KPIB =
VAR T1 =
SELECTEDVALUE ( 'Measure'[INDEX] )
VAR T2 =
SWITCH (
TRUE (),
T1 = 01, SELECTEDVALUE ( '分店表B'[分店名] ),
T1 = 02, [FACT_01_销售数量B],
T1 = 03, [FACT_02_成交订单B],
T1 = 04, [FACT_03_顾客数量B],
T1 = 05, [FACT_04_销售金额B],
T1 = 06, [FACT_05_销售成本B],
T1 = 07, [FACT_06_销售利润B]
)
RETURN
T2``````

（BOSS：干得漂亮！）

（文件在知识星球[PowerBI丨需求圈]）

https://my.oschina.net/u/4375980/blog/4777258