在销售团队管理中,衡量销售员的业绩稳定性是一个重要指标。其中,"最大连续业绩达标月数"能够直观反映销售员持续达成业绩目标的能力,比单纯的累计达标次数更具参考价值。本篇文章将介绍如何在PowerBI中实现这一关键指标的计算。
问题描述
为分析销售员的业绩稳定情况,现在需要计算各销售员在各个月份中的达标次数与最大连续业绩达标月数。
达标次数:当月达标则为1,不达标为0,总计或小计中返回达标次数的汇总。
最大连续业绩达标月数:统计范围为当前月份及之前的滚动时间段,总计或小计中返回明细行中最大的连续业绩达标月数。
具体问题如下图所示:
本案例的初始数据如下:
SalesPerson | YearMonth | SalesAmount | SalesTarget |
---|---|---|---|
Alice | 202501 | 520 | 500 |
Alice | 202502 | 480 | 500 |
Alice | 202503 | 600 | 500 |
Alice | 202504 | 700 | 500 |
Alice | 202505 | 400 | 500 |
Alice | 202506 | 550 | 500 |
Bob | 202501 | 480 | 500 |
Bob | 202502 | 510 | 500 |
Bob | 202503 | 520 | 500 |
Bob | 202504 | 490 | 400 |
Bob | 202505 | 530 | 500 |
Bob | 202506 | 540 | 500 |
Charlie | 202501 | 500 | 500 |
Charlie | 202502 | 500 | 500 |
Charlie | 202503 | 500 | 500 |
Charlie | 202504 | 490 | 500 |
Charlie | 202505 | 495 | 500 |
Charlie | 202506 | 510 | 500 |
若需其它辅助表等,可自行创建并建模。
解题要点
本案例的关键点其实就在于最大连续元素数的计算,这里给出三种思路的解法:
1、迭代起始取最大法,其思路如下表所示:
SalesPerson | YearMonth | IsAchieved | StartMonth (<CurMonth&&IsAchieved<>1) | Month Num |
---|---|---|---|---|
Alice | 202501 | 1 | BLANK | BLANK<Month<=202501 |
Alice | 202502 | 0 | ||
Alice | 202503 | 1 | 202502 | 202502<Month<=202503 |
Alice | 202504 | 1 | 202502 | 202502<Month<=202504 |
Alice | 202505 | 0 | ||
Alice | 202506 | 1 | 202505 | 202505<Month<=202506 |
2、反转累计分组法,其思路如下表所示:
SalesPerson | YearMonth | IsAchieved | MOD(IsAchieved+1,2) | Accumulate |
---|---|---|---|---|
Alice | 202501 | 1 | 0 | 0 |
Alice | 202502 | 0 | 1 | 1 |
Alice | 202503 | 1 | 0 | 1 |
Alice | 202504 | 1 | 0 | 1 |
Alice | 202505 | 0 | 1 | 2 |
Alice | 202506 | 1 | 0 | 2 |
3、减排名分组法,其思路如下表所示:
SalesPerson | YearMonth | IsAchieved | Rank | MonthIndex (Year*12+Month) | Grp (MonthIndex-Rank) |
---|---|---|---|---|---|
Alice | 202501 | 1 | 1 | 24301 | 24300 |
Alice | 202502 | 0 | |||
Alice | 202503 | 1 | 2 | 24303 | 24301 |
Alice | 202504 | 1 | 3 | 24304 | 24301 |
Alice | 202505 | 0 | |||
Alice | 202506 | 1 | 4 | 24306 | 24302 |
解决方案
首先,数据模型如下图所示:
然后,创建如下度量值,其中关于最大连续业绩达标月数指标的计算方法,这里提供三种:
达标次数 = SUMX('Fact',('Fact'[SalesAmount]>='Fact'[SalesTarget])*1)
最大连续业绩达标月数1 =
MAXX(
VALUES('Fact'[SalesPerson]),
VAR DataTbl =
CALCULATETABLE(
ADDCOLUMNS('Fact',"IsAchieved",('Fact'[SalesAmount]>='Fact'[SalesTarget])*1),
'Fact'[YearMonth]<=MAX('Fact'[YearMonth])
)
RETURN
MAXX(
DataTbl,
IF(
[IsAchieved]=1,
VAR CurMonth = 'Fact'[YearMonth]
VAR StartMonth = MAXX(FILTER(DataTbl,'Fact'[YearMonth]<CurMonth && [IsAchieved]<>1),'Fact'[YearMonth])
RETURN
COUNTROWS(FILTER(DataTbl,'Fact'[YearMonth]>StartMonth && [YearMonth]<=CurMonth))
)
)+0
)
最大连续业绩达标月数2 =
MAXX(
VALUES('Fact'[SalesPerson]),
VAR DataTbl =
CALCULATETABLE(
ADDCOLUMNS('Fact',"IsAchieved",('Fact'[SalesAmount]>='Fact'[SalesTarget])*1),
'Fact'[YearMonth]<=MAX('Fact'[YearMonth])
)
RETURN
MAXX(
SUMMARIZE(
ADDCOLUMNS(
DataTbl,
"grp",
SUMX(
FILTER(DataTbl,'Fact'[YearMonth]<=EARLIER('Fact'[YearMonth])),
MOD([IsAchieved]+1,2)
)
),
[grp],
"Num",IF([grp]=0,COUNTROWS('Fact'),COUNTROWS('Fact')-1)
),
[Num]
)+0
)
最大连续业绩达标月数3 =
MAXX(
VALUES('Fact'[SalesPerson]),
VAR DataTbl =
CALCULATETABLE(
FILTER(
ADDCOLUMNS('Fact',"IsAchieved",('Fact'[SalesAmount]>='Fact'[SalesTarget])*1),
[IsAchieved]=1
),
'Fact'[YearMonth]<=MAX('Fact'[YearMonth])
)
RETURN
MAXX(
SUMMARIZE(
ADDCOLUMNS(
DataTbl,
"Grp",
VAR MonthIndex = LEFT('Fact'[YearMonth],4)*12+RIGHT('Fact'[YearMonth],2)
VAR RankIndex = RANKX(DataTbl,'Fact'[YearMonth],,1)
RETURN
MonthIndex-RankIndex
),
[Grp],
"Num", COUNTROWS('Fact')
),
[Num]
)+0
)
然后创建一个矩阵,并将月份和销售员字段作为行标签,再将上面的度量值放入矩阵的值字段即可,如下图所示:
总结
以上方法仅供参考,若有更优雅的解决方案,欢迎留言讨论,或者加入我们的技术交流群,一起享受这种思维碰撞的快乐吧!
PBI/DAX技术交流群(QQ):344353627