今天在QQ群看见一个求助,要求把原始数据分类统计:
生成的结果:
题主的要求是用公式做统计表,我是想用DAX做这个统计表,就有了今天这篇文章。
首先来分析一下原始数据,因为咱也不是通讯行业的,对这些术语也不了解,但是根据数据之间的关系,大概可以看出,这个数据是经过整理的,但是不够完美,倒数第三列的数据应该是核心数据,后面两列是根据这一列整理出来的。
所以这份数据存在两个问题:
- 数值与文字混合在同一列,这个不符合Power Query或者Power Pivot中对数据的要求。
- 数据关系有些混乱,低流量与零流量有重合,又把文本放进来。
其实应该从倒数第三列中分析出4列数据:
- 零流量:倒数第三列中为0的对应行
- 低流量:倒数第三列中0-100对应的行
- 正常:倒数第三列中数值大于100的行
- 北向无性能上报:倒数第三列中文本对应的行
根据这些分析,我们来做这个工作,其实在Excel中也是一样的,因为Power BI Desktop数据演示比较方便,就用Power BI Desktop
Power Query数据处理
我们把数据导入到Power Query编辑器中,删除最后两列,用我们分析的方法,重新整理数据:
零流量列,等于0的行为1,其他行为null。
低流量列:文本数值混合的行处理起来容易报错,注意其中try的写法,利用错误值进行计算,数值在0-100的行为1,其他行为null。
北向无性能上报列:
正常列:上述三类情况之外的所有行
定义之后的结果是这样的:
然后这后面4列做逆透视,逆透视之后再修改一下数据类型就可以了:
其实我们最需要的并不是最后的值这一列,属性这一列最关键,我们可以直接COUNTROWS属性这一列,就能得到我们想要的结果,当然,有了值这一列,我们直接SUM值这一列也是一样的。
DAX度量值
我们之前分析过,零流量与低流量有重复,我们先来看一下属性这一列的相互关系:
低流量包含零流量,我们处理过的数据比原有数据多出来了386行,就是零流量对应的行。
我们先新建一个度量值表:
然后在这个表里写度量值,这是Power BI Desktop中的编写度量值的习惯,如果是在Excel中的Power Pivot里直接在表最下方的格子里写就可以了。
我们今天要写的度量值都很简单,最基础的写法:
度量值:总计数
总计数 = SUM('数据源'[值])
度量值:零流量
零流量 = CALCULATE([总计数],'数据源'[属性]="零流量")
度量值:低流量
低流量 = CALCULATE([总计数],'数据源'[属性]="低流量")
度量值:北向无性能上报
北向无性能上报 = CALCULATE([总计数],'数据源'[属性]="北向无性能上报")
度量值:非承载
非承载 = [低流量]+[北向无性能上报]
度量值:总计
总计 = [总计数]-[零流量]
度量值:非承载占比
非承载占比 = IFERROR(DIVIDE([非承载],[总计]),0)
对于我们初学者来说,度量值可以这样递进地写,先写一个总计数,然后其他度量值用这个总计数来写。
数据呈现
在Excel中就可以从数据模型添加透视表或者透视图,在Power BI Desktop中就直接用视觉对象来展示数据,就用矩阵表就可以了:
优化组放到行,写好的度量值一个一个地放到值的位置就可以了:
第二张表要添加一个筛选器,去掉高校行:
这样虽然在同一页面,相同的度量值也可以显示出不同的结果:
那位同学肯定要说了,明明几个COUNTIFS就能搞定的问题,非得弄得这么复杂,其实这样折腾一下,这个可以称作是一个小的数据模型,以后再有相同的问题,直接更改数据源,刷新数据就能出结果。