Forum Discussion

UKNSI-Powerbi's avatar
UKNSI-Powerbi
Frequent Visitor
3 years ago

Row variance calculation

Hi,   We have a table and we hope to be able to calcuate row. variance.    Example  Dec 20 Variance between 15/12 (480) and 01/12 (432) is (480-432)/432 = 11%   Date Ref Rev Dec-20 Jan-...
Need Help
UKNSI-Powerbi's avatar
UKNSI-Powerbi
Frequent Visitor
3 years ago

Hi v-jingzhang 

 

It seems my post need further details.

 

At the moment I have a table of customer cumulations grouped by  Cust/Seq/Contract/Part/Date/Release/YYYYMM

 

CustSeqContractPartDateReleaseRevYYYYMMCUMULATION
CUST1    10035987PART1    01/12/2020650202012432
CUST1    10035987PART1    02/12/2020660202012432
CUST1    10035987PART1    03/12/2020670202012424
CUST1    10035987PART1    07/12/2020680202012424
CUST1    10035987PART1    08/12/2020690202012432
CUST1    10035987PART1    09/12/2020700202012480
CUST1    10035987PART1    10/12/2020710202012480
CUST1    10035987PART1    15/12/2020720202012480
CUST1    10035987PART1    16/12/2020730202012480
CUST1    10035987PART1    01/12/2020650202101816
CUST1    10035987PART1    02/12/2020660202101816
CUST1    10035987PART1    03/12/2020670202101808
CUST1    10035987PART1    07/12/2020680202101808
CUST1    10035987PART1    08/12/2020690202101816
CUST1    10035987PART1    09/12/2020700202101888
CUST1    10035987PART1    10/12/2020710202101888
CUST1    10035987PART1    15/12/2020720202101888
CUST1    10035987PART1    16/12/2020730202101816
CUST1    10035987PART1    17/12/2020740202101816
CUST1    10035987PART1    17/12/2020750202101

816

 

On PBI the matrix is looking like this

 

 

I would like to be able to calculate the variance in PBI for the same group Cust/Seq/Contract/Part/Date/Release/YYYYMM day by day.

 

Example 

CustSeqContractPartDateReleaseRevYYYYMMCUMULATIONVariance
CUST1    10035987PART1    01/12/2020650202012432 
CUST1    10035987PART1    02/12/20206602020124320%
CUST1    10035987PART1    03/12/2020670202012424-2%
CUST1    10035987PART1    07/12/20206802020124240%
CUST1    10035987PART1    08/12/20206902020124322%
CUST1    10035987PART1    09/12/202070020201248011%
CUST1    10035987PART1    10/12/20207102020124800%
CUST1    10035987PART1    15/12/20207202020124800%
CUST1    10035987PART1    16/12/20207302020124800%
CUST1    10035987PART1    01/12/202065020210181670%
CUST1    10035987PART1    02/12/20206602021018160%
CUST1    10035987PART1    03/12/2020670202101808-1%
CUST1    10035987PART1    07/12/20206802021018080%
CUST1    10035987PART1    08/12/20206902021018161%
CUST1    10035987PART1    09/12/20207002021018889%
CUST1    10035987PART1    10/12/20207102021018880%
CUST1    10035987PART1    15/12/20207202021018880%
CUST1    10035987PART1    16/12/2020730202101816-8%
CUST1    10035987PART1    17/12/20207402021018160%
CUST1    10035987PART1    17/12/20207502021018160%

 

Your help will be really appreciate.

Thanks 

Joe