如何计算按月交易量的每日百分比？ [英] How to calculate daily percentage over month on month volume?

问题描述

我正在尝试根据给定数据中某些记录的月份和年份来对量进行求和，然后针对给定的月份来计算每日到达功率bi的百分比

I am trying to sum the volume based on month and year for certain records in the given data and then for the given month I am trying to calculate the daily percentage arrival in power bi

用于记录人员A，1/1/2018，Project，100,1 应该是100/220

for the record Person A,1/1/2018,Project,100,1 it should be 100/220

下面是示例数据：

Resource Name,Month,RecordType,Actual,Flag
Person A,1/1/2018,Project,100,1
Person A,1/2/2018,Support,40,1
Person A,1/3/2018,Training,50,1
Person A,1/4/2018,Unavailable,30,1
Person A,2/1/2018,Project,80,1
Person A,2/2/2018,Support,50,1
Person A,2/3/2018,Training,20,1
Person A,2/4/2018,Unavailable,50,1
Person A,3/1/2018,Project,150,1
Person A,3/2/2018,Support,10,1
Person A,3/3/2018,Training,20,1
Person A,3/4/2018,Unavailable,20,1
Person B,1/1/2018,Project,50,0
Person B,1/1/2018,Support,100,0
Person B,1/1/2018,Training,45,0
Person B,1/1/2018,Unavailable,5,0
Person B,2/1/2018,Project,90,0
Person B,2/1/2018,Support,80,0
Person B,2/1/2018,Training,10,0
Person B,2/1/2018,Unavailable,20,0
Person B,3/1/2018,Project,180,0
Person B,3/1/2018,Support,5,0
Person B,3/1/2018,Training,10,0
Person B,3/1/2018,Unavailable,5,0

在下面尝试过但出错了

  Daily_percentage1 = CALCULATE(data1[Actual])/CALCULATE(SUM(data1[Actual]),FILTER(ALLEXCEPT(data1,data1[Month].[Month],data1[Month].[Year]),data1[Flag]=1))

加速输出

Resource Name,RecordType,Flag,Month,Actual,Daily_percentage1
Person A,Project,1,1/1/2018 0:00,100,0.454545455
Person A,Support,1,1/2/2018 0:00,40,0.181818182
Person A,Training,1,1/3/2018 0:00,50,0.227272727
Person A,Unavailable,1,1/4/2018 0:00,30,0.136363636
Person A,Project,1,2/1/2018 0:00,80,0.4
Person A,Support,1,2/2/2018 0:00,50,0.25
Person A,Training,1,2/3/2018 0:00,20,0.1
Person A,Unavailable,1,2/4/2018 0:00,50,0.25
Person A,Project,1,3/1/2018 0:00,150,0.75
Person A,Support,1,3/2/2018 0:00,10,0.05
Person A,Training,1,3/3/2018 0:00,20,0.1
Person A,Unavailable,1,3/4/2018 0:00,20,0.1
Person B,Project,0,1/1/2018 0:00,50,
Person B,Support,0,1/1/2018 0:00,100,
Person B,Training,0,1/1/2018 0:00,45,
Person B,Unavailable,0,1/1/2018 0:00,5,
Person B,Project,0,2/1/2018 0:00,90,
Person B,Support,0,2/1/2018 0:00,80,
Person B,Training,0,2/1/2018 0:00,10,
Person B,Unavailable,0,2/1/2018 0:00,20,
Person B,Project,0,3/1/2018 0:00,180,
Person B,Support,0,3/1/2018 0:00,5,
Person B,Training,0,3/1/2018 0:00,10,
Person B,Unavailable,0,3/1/2018 0:00,5,

推荐答案

每个人都有自己的选择，我想用一种方法来处理，而不是单独使用2种

Everyone has their own choice, I would like to handle this in one measure rather having 2 separate measures.

Total_Percentage = 
VAR TotalPerMonth =
    CALCULATE (
        SUM ( data1[Actual] ),
        FILTER ( data1, data1[Month].[Month] = EARLIER ( data1[Month].[Month] ) ),
        FILTER ( data1, data1[Month].[Year] = EARLIER ( data1[Month].[Year] ) ),
        FILTER ( data1, data1[Flag] = 1 )
    )
RETURN
    DIVIDE ( data1[actual], TotalPerMonth, 0 )
// taking care of divide by zero error

这篇关于如何计算按月交易量的每日百分比？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！