最简单的网站怎么做,网站建设维护专员,wordpress 灯鹭,福清可以做宣传的网站166_技巧_Power BI 窗口函数处理连续发生业务问题
一、背景
在生产经营的数据监控中#xff0c;会有一类指标需要监控是否连续发生#xff0c;从而根据其在设定区间中的连续频次来评价业务。
例如#xff1a;
员工连续迟到天数。销售金额连续上升或者下降。用户连续登陆…166_技巧_Power BI 窗口函数处理连续发生业务问题
一、背景
在生产经营的数据监控中会有一类指标需要监控是否连续发生从而根据其在设定区间中的连续频次来评价业务。
例如
员工连续迟到天数。销售金额连续上升或者下降。用户连续登陆天数。找出设定区间符合销售金额连续增长产品明细诸如此类…
在前面的文章其实已经对此类问题做了相应的演示(https://jiaopengzi.com/392.html)。
本次我们使用 Power BI 在 2022 年 12 月份更新的窗口函数来处理找出设定区间符合销售金额连续增长产品明细 的问题。关于窗口函数的信息可以参考 Jeffrey Wang 在博客(https://pbidax.wordpress.com/2022/12/15/introducing-dax-window-functions-part-1/) 中的说明已经非常的详细。本文我们将不花篇幅去讲解窗口函数了。我们重点来讲讲通过 DAX 去解决业务问题。
按照惯例还是先来看看结果
Power BI 公共 web 效果https://demo.jiaopengzi.com/pbi/166-full.html 二、数据源及业务问题
首先感谢网友 俊 提供数据文中已经对数据进行了脱敏。
1、数据源
数据源非常简单就一个销售数据表其中包含日期、产品ID、销售金额 2、关系
建模必备的日期表同时把 产品ID 单独拎出来做维度表建立表间关系。 三、DAX
1、销售金额
基础度量没有什么好说的。
0000_销售金额 SUM ( 销售数据[销售金额] )2、是否符合要求
0001是否符合要求
VAR ROW_MAX /*当前事实表行数。*/COUNTROWS ( 销售数据 )
VAR T1 /*准备好窗口函数需要的表格。*/SUMMARIZE ( ALLSELECTED ( 销售数据 ), 销售数据[产品ID], A00_Calendar[C01_Dates], 销售数据[销售金额] )
VAR T2 /* 1、根据产品ID和日期提前做好排序。2、需要注意这里使用的是绝对应用之所以使用 N是因为不会有比 N 更大的行数。3、因为要对每个 SKU 单独排序所以要使用 PARTITIONBY。*/WINDOW (1,ABS,ROW_MAX,ABS,T1,ORDERBY ( 销售数据[产品ID], ASC, A00_Calendar[C01_Dates], ASC ),KEEP,PARTITIONBY ( 销售数据[产品ID] ))
VAR T3 /*使用 OFFSET 偏移一行实现错位拿到 N1 的数据。*/ADDCOLUMNS (T2,N1,CALCULATE ([0000_销售金额],T1,OFFSET (1,T2,ORDERBY ( 销售数据[产品ID], ASC, A00_Calendar[C01_Dates], ASC ),KEEP,PARTITIONBY ( 销售数据[产品ID] ))))
VAR T4 /*错位后N1 大于成交金额金额即为满足增加。*/ADDCOLUMNS ( T3, 是否递增, IF ( [N1] [销售金额], 1, 0 ) )
VAR T5 /*1、计算每个 SKU 记录数量 count。2、计算每个 SKU 总共与多少是符合递增的。*/SUMMARIZE (T1,[产品ID],count, VAR SKU [产品ID] VAR T FILTER ( T1, [产品ID] SKU ) RETURN COUNTROWS ( T ),growth, VAR SKU [产品ID] VAR T FILTER ( T4, [产品ID] SKU ) RETURN SUMX ( T, [是否递增] ))
VAR T6 /*1、依据业务的要求需要满足记录数大于5则 count 52、满足连续递增曾则表示表示 SKU 记录数量 count 和 满足递增爽 growth 差 1 即可第一期是没有计算的递增的。*/FILTER ( T5, [count] - 1 [growth] [count] 5 )
VAR TF /*最有是要找出这些 SKU 那么输出表在不建立计算表的情况下吗那么就把使用度量值的设置是否等于1来实现筛选即可*/IF ( VALUES ( 产品表[产品ID] ) IN SELECTCOLUMNS ( T6, 产品ID, [产品ID] ), 1, 0 )
RETURN/*符合要求度量值结果为 1 不符合要求度量值结果为 0*/TF在上图 166-1 中我们可以看到业务需求如下找出成交金额随着日期连续大于5期递增的产品ID找出产品ID后计算出最小粒度日期环比(说明数据源的的产片销售日期不一定连续)
注意这里的度量值是否符合要求我们是放到了切片器的视觉对象筛选器中切片器的字段是产品ID。这里利用了度量值是否等于1的结果来判断是否符合要求。
我们通过切片器切换可以看到都是符合要求产品ID表格所在区间都是符合连续递增的。 环比上期
环比上期度量值中我们可以看到这个是一个日期维度的比较我们并没有用到时间智能函数同时注意结果中的日期都是不连续的。如果使用时间智能函数其实相对还比较麻烦。所以窗口函数在 DAX 中是一把利刃。
0002_环比上期
VAR FZ [0000_销售金额]
VAR T0 SUMMARIZE ( ALLSELECTED ( 销售数据 ), 产品表[产品ID], A00_Calendar[C01_Dates] )
VAR FM CALCULATE ([0000_销售金额],OFFSET ( -1, T0, ORDERBY ( A00_Calendar[C01_Dates], ASC ) ))
VAR _PERCENT DIVIDE ( FZ - FM, FM )
VAR RESULT IF ( HASONEFILTER ( A00_Calendar[C01_Dates] ), _PERCENT, BLANK () )
RETURNRESULT四、关于连续发生事件的分步解析。 在 DAX Studio 中使用如下 DAX 代码去分布理解其实也就是上述 “是否符合要求” 的度量值中的核心部分。
在结合注释理解连续发生问题的处理过程。就是把这类连续发生问题首先通过排序来抽象成 1 或者 0当然也可以抽象成 YES 或者 NO 抽象为 1 和 0 在 DAX 计算中会更加方便。
其实这里面最主要的问题是在 DAX 中生成的过程表要能排序是非常困难的当然也可以结合前面的文章来看(https://jiaopengzi.com/392.html),现在有了窗口函数也就更加得心应手了。
DEFINE
VAR ROW_MAX /*当前事实表行数。*/COUNTROWS ( 销售数据 )
VAR T1 /*准备好窗口函数需要的表格。*/SUMMARIZE ( ALLSELECTED ( 销售数据 ), 销售数据[产品ID], A00_Calendar[C01_Dates], 销售数据[销售金额] )
VAR T2 /* 1、根据产品ID和日期提前做好排序。2、需要注意这里使用的是绝对应用之所以使用 N是因为不会有比 N 更大的行数。3、因为要对每个 SKU 单独排序所以要使用 PARTITIONBY。*/WINDOW (1,ABS,ROW_MAX,ABS,T1,ORDERBY ( 销售数据[产品ID], ASC, A00_Calendar[C01_Dates], ASC ),KEEP,PARTITIONBY ( 销售数据[产品ID] ))
VAR T3 /*使用 OFFSET 偏移一行实现错位拿到 N1 的数据。*/ADDCOLUMNS (T2,N1,CALCULATE ([0000_销售金额],T1,OFFSET (1,T2,ORDERBY ( 销售数据[产品ID], ASC, A00_Calendar[C01_Dates], ASC ),KEEP,PARTITIONBY ( 销售数据[产品ID] ))))
VAR T4 /*错位后N1 大于成交金额金额即为满足增加。*/ADDCOLUMNS ( T3, 是否递增, IF ( [N1] [销售金额], 1, 0 ) )
VAR T5 /*1、计算每个 SKU 记录数量 count。2、计算每个 SKU 总共与多少是符合递增的。*/SUMMARIZE (T1,[产品ID],count, VAR SKU [产品ID] VAR T FILTER ( T1, [产品ID] SKU ) RETURN COUNTROWS ( T ),growth, VAR SKU [产品ID] VAR T FILTER ( T4, [产品ID] SKU ) RETURN SUMX ( T, [是否递增] ))
VAR T6 /*1、依据业务的要求需要满足记录数大于5则 count 52、满足连续递增曾则表示表示 SKU 记录数量 count 和 满足递增爽 growth 差 1 即可第一期是没有计算的递增的。*/FILTER ( T5, [count] - 1 [growth] [count] 5 )
EVALUATE
T1
EVALUATE
T2
EVALUATE
T3
EVALUATE
T4
EVALUATE
T5
EVALUATE
T6五、总结
1、本文使用 DAX 窗口函数处理连续发生问题的排序也可以在 pq 中去处理也能在 sql 一步到位。方式和工具还是比较多一切工具都是为了业务服务的。
2、窗口函数在不连续的日期对比或者非日期的对比中都能实现 极大的增加 Power BI 了数据的灵活性。
3、本文案例中我们需求的是整个数据表为监控的区间大家要能做到举一反三如果需要监控不同日期区间或者不同产品ID区间只需要在在第一步中构造这样区间的上下文即可在外部赋予它即可。
附件下载
https://jiaopengzi.com/2902.html
视频课
https://jiaopengzi.com/all-course
by 焦棚子
