PowerBi–topn+帕累托
(此数据虚构)仅供学习,不提供数据,谢谢
思考:其实基本上很多现象都满足二八定律,所以利用PowerBi的动态交互式图表定制出一份报告:基于TOPN+帕累托来分析不同KPI数据的二八定律,分析出贡献80%价值的20%商品/渠道
注:这里对表之间建立关系没有大要求(因为有用TREATAS),时间表和其他表建好关系就好
DAX公式:
-------------------------建表----------------------------------------------------------------------------------------
#建立TOPN参数表
TOPN参数表 =
VAR items1 = UNION(SELECTCOLUMNS(VALUES('商品分类'[品类]),"TOPN参数",[品类]),{"其他"})
VAR items1_cross =CROSSJOIN({"商品"},items1)
VAR items2 =UNION(SELECTCOLUMNS(VALUES('店铺流量来源'[来源明细]),"TOPN参数",[来源明细]),{"其他"})
VAR items2_cross=CROSSJOIN({"渠道"},items2)
Return UNION(items1_cross,items2_cross)
#建立KPI类别表
KPI类别表 = SELECTCOLUMNS({("商品","流量",1),("商品","销售额",2),("商品","支付人数",3),("商品","新客户",4),("渠道","流量",5),("渠道","新流量",6),("渠道","销售额",7),("渠道","支付人数",8),("recent","流量",9),("recent","销售额",10),("recent","支付人数",11),("recent","转化率",12),("recent","新客户",13),("recent","新流量",14)},"类别",[Value1],"KPI",[Value2],"orderby",[Value3])
-------------------------写DAX公式------------------------------------------------------------------------------
#基础的度量值这里就不加了,很简单的,主要分享关键度量值
#商品KPI数据
kpi_auto_商品 = SWITCH(SELECTEDVALUE('KPI类别表'[KPI]),"销售额",[2支付金额],"支付人数",[2支付人数],"流量",[2流量],"新客户",[2新支付人数])
#渠道KPI数据
kpi_auto_渠道 = SWITCH(SELECTEDVALUE('KPI类别表'[KPI]),"销售额",[渠道_销售额],"支付人数",[渠道_支付人数],"新流量",[渠道_新访客],"流量",[渠道_访客数])
#返回商品/渠道
维度_index = SELECTEDVALUE('TOPN参数表'[维度])
#如果维度_index=商品,返回kpi_auto_商品,否则返回kpi_auto_渠道,TREATAS函数是在无关系情况下执行查找匹配
kpi_auto =
var index1 = CALCULATE([kpi_auto_商品],TREATAS(VALUES('TOPN参数表'[TOPN参数]),'商品分类'[品类]))
var index2 = CALCULATE([kpi_auto_渠道],TREATAS(VALUES('TOPN参数表'[TOPN参数]),'店铺流量来源'[来源明细]))
return SWITCH([维度_index],"渠道",index2,"商品",index1)
#一般的排序公式
rank = RANKX(ALL('TOPN参数表'[TOPN参数]),[kpi_auto])
#如果参数是其他,rank=51,否则按[rank]执行
view.rank =
VAR category= SELECTEDVALUE ( 'TOPN参数表'[TOPN参数] )
RETURN SWITCH ( TRUE(), HASONEVALUE ( 'TOPN参数表'[TOPN参数] ) && [view.topn] <> 0 && [rank] <= [参数N值], [rank], HASONEVALUE ( 'TOPN参数表'[TOPN参数] ) && [view.topn] <> 0 && category = "其他", 51 )
#返回topn的KPI值,如果参数是"其他",那么求没有被选择的商品/渠道的KPI总值
view.topn =
VAR category = SELECTEDVALUE('TOPN参数表'[TOPN参数])
VAR n1 = TOPN([参数N值],VALUES('商品分类'[品类]),[kpi_auto_商品])
VAR n2 = TOPN([参数N值],VALUES('店铺流量来源'[来源明细]),[kpi_auto_渠道])
var re1 = sWITCH(TRUE(),[rank]<=[参数N值],[kpi_auto],category="其他",CALCULATE([kpi_auto_商品],EXCEPT(ALLSELECTED('商品分类'[品类]),n1)))
var re2 = sWITCH(TRUE(),[rank]<=[参数N值],[kpi_auto],category="其他",CALCULATE([kpi_auto_渠道],EXCEPT(ALLSELECTED('店铺流量来源'[来源明细]),n2)))
return SWITCH([维度_index],"渠道",re2,"商品",re1)
#商品/渠道的贡献比例
帕累托_占比 = if([维度_index]="商品",DIVIDE([view.topn],[kpi_auto_商品],0) ,DIVIDE([view.topn],[kpi_auto_渠道],0))
#占比的累计值,从而分析出贡献80%的商品/渠道是哪些,其实就是帕累托图,但是这个数据的top3占比太大,不适合用帕累托
帕累托_累计 = VAR per = [帕累托_占比]
VAR tot = CALCULATE([view.topn],FILTER(ALL('TOPN参数表'[TOPN参数]),'index_table'[帕累托_占比]>=per))
VAR tot1 = if([维度_index]="渠道",IF(NOT ISBLANK([view.topn]),DIVIDE(TOT,[kpi_auto_渠道])),IF(NOT ISBLANK([view.topn]),DIVIDE(TOT,[kpi_auto_商品])))
RETURN if(SELECTEDVALUE('TOPN参数表'[TOPN参数])="其他",BLANK(),tot1)
#累计占比80%的
帕累托_累计_A = if([帕累托_累计]<=0.8,[帕累托_累计])