Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi
One of the data sources I am a reading into Power BI is an Excel file which contains production rate data for both monthly average and yearly average rates values in the same column (this is a requirement for a separate database it gets loaded into). I would like to calculate the yearly average rate from the monthly values, to be able to compare it to other data I am reading in. The calcualtion needs to be made in PowerBI.
The data looks like this (dummy example below left) - I have added a column called "DateType" where I identify what is a monthly and what is a yearly average, as well a number of days "NoDays" (as I would have done a "SUMPRODUCT(rate; days)/SUM(days)" for this type of calculation in Excel - a simple average will come out wrong as each month has different number of days).
I would like to end up with the below right.
Any help is greatly appreciated. Sophie
Hi @Anonymous,
You could create a calculated table with below formula:
Table = VAR temptable = ADDCOLUMNS ( rate, "NewCol1", rate[Oil rate (m3/d)] * rate[NoDays] ) VAR tempTable2 = GROUPBY ( temptable, [Date].[Year], "TotalRate", SUMX ( CURRENTGROUP (), [NewCol1] ), "NoDays", SUMX ( CURRENTGROUP (), [NoDays] ) ) RETURN ADDCOLUMNS ( tempTable2, "Avg", [TotalRate] / [NoDays], "Type", "Yearly" )
Best regards,
Yuliana Gu
Thank you, That works perfectly!
I do have a further challenge.
The data of course is not so simple. I have numerous subprojects, different vintages of the data (budget) as well as additional rate types (oil, gas, water … )
I will paste some sample data in the next reply I don’t seem to be able to upload directly here.
The output I would like:
Sample data
Subproject | Budget | Date | Oil rate (m3/d) | Gas rate (m3 gas/d) | NGL (m3/d) | BOE (m3/d) | DateType | NoDays |
Alpha | 2Q2018 | 01.01.2018 | 4750 | 9500000 | 1995 | 16245 | monthly | 31 |
Alpha | 2Q2018 | 01.02.2018 | 4560 | 9120000 | 1915.2 | 15595.2 | monthly | 28 |
Alpha | 2Q2018 | 01.03.2018 | 4350 | 8700000 | 1827 | 14877 | monthly | 31 |
Alpha | 2Q2018 | 01.04.2018 | 4200 | 8400000 | 1764 | 14364 | monthly | 30 |
Alpha | 2Q2018 | 01.05.2018 | 4000 | 8000000 | 1680 | 13680 | monthly | 31 |
Alpha | 2Q2018 | 01.06.2018 | 3670 | 7340000 | 1541.4 | 12551.4 | monthly | 30 |
Alpha | 2Q2018 | 01.07.2018 | 3810 | 7620000 | 1600.2 | 13030.2 | monthly | 31 |
Alpha | 2Q2018 | 01.08.2018 | 3500 | 7000000 | 1470 | 11970 | monthly | 31 |
Alpha | 2Q2018 | 01.09.2018 | 4230 | 8460000 | 1776.6 | 14466.6 | monthly | 30 |
Alpha | 2Q2018 | 01.10.2018 | 3770 | 7540000 | 1583.4 | 12893.4 | monthly | 31 |
Alpha | 2Q2018 | 01.11.2018 | 3400 | 6800000 | 1428 | 11628 | monthly | 30 |
Alpha | 2Q2018 | 01.12.2018 | 3800 | 7600000 | 1596 | 12996 | monthly | 31 |
Beta | 2Q2018 | 01.01.2019 | 3960 | 4752000 | 997.92 | 9709.92 | monthly | 31 |
Beta | 2Q2018 | 01.02.2019 | 4110 | 4932000 | 1035.72 | 10077.72 | monthly | 28 |
Beta | 2Q2018 | 01.03.2019 | 4150 | 4980000 | 1045.8 | 10175.8 | monthly | 31 |
Beta | 2Q2018 | 01.04.2019 | 3980 | 4776000 | 1002.96 | 9758.96 | monthly | 30 |
Beta | 2Q2018 | 01.05.2019 | 3560 | 4272000 | 897.12 | 8729.12 | monthly | 31 |
Beta | 2Q2018 | 01.06.2019 | 3760 | 4512000 | 947.52 | 9219.52 | monthly | 30 |
Beta | 2Q2018 | 01.07.2019 | 3000 | 3600000 | 756 | 7356 | monthly | 31 |
Beta | 2Q2018 | 01.08.2019 | 2900 | 3480000 | 730.8 | 7110.8 | monthly | 31 |
Beta | 2Q2018 | 01.09.2019 | 3365 | 4038000 | 847.98 | 8250.98 | monthly | 30 |
Beta | 2Q2018 | 01.10.2019 | 3380 | 4056000 | 851.76 | 8287.76 | monthly | 31 |
Beta | 2Q2018 | 01.11.2019 | 3000 | 3600000 | 756 | 7356 | monthly | 30 |
Beta | 2Q2018 | 01.12.2019 | 2900 | 3480000 | 730.8 | 7110.8 | monthly | 31 |
Alpha | 1Q2018 | 01.01.2018 | 4800 | 9600000 | 2016 | 16416 | monthly | 31 |
Alpha | 1Q2018 | 01.02.2018 | 4610 | 9220000 | 1936.2 | 15766.2 | monthly | 28 |
Alpha | 1Q2018 | 01.03.2018 | 4400 | 8800000 | 1848 | 15048 | monthly | 31 |
Alpha | 1Q2018 | 01.04.2018 | 4250 | 8500000 | 1785 | 14535 | monthly | 30 |
Alpha | 1Q2018 | 01.05.2018 | 4050 | 8100000 | 1701 | 13851 | monthly | 31 |
Alpha | 1Q2018 | 01.06.2018 | 3720 | 7440000 | 1562.4 | 12722.4 | monthly | 30 |
Alpha | 1Q2018 | 01.07.2018 | 3860 | 7720000 | 1621.2 | 13201.2 | monthly | 31 |
Alpha | 1Q2018 | 01.08.2018 | 3550 | 7100000 | 1491 | 12141 | monthly | 31 |
Alpha | 1Q2018 | 01.09.2018 | 4280 | 8560000 | 1797.6 | 14637.6 | monthly | 30 |
Alpha | 1Q2018 | 01.10.2018 | 3820 | 7640000 | 1604.4 | 13064.4 | monthly | 31 |
Alpha | 1Q2018 | 01.11.2018 | 3450 | 6900000 | 1449 | 11799 | monthly | 30 |
Alpha | 1Q2018 | 01.12.2018 | 3850 | 7700000 | 1617 | 13167 | monthly | 31 |
Beta | 1Q2018 | 01.01.2019 | 4060 | 4872000 | 1023.12 | 9955.12 | monthly | 31 |
Beta | 1Q2018 | 01.02.2019 | 4210 | 5052000 | 1060.92 | 10322.92 | monthly | 28 |
Beta | 1Q2018 | 01.03.2019 | 4250 | 5100000 | 1071 | 10421 | monthly | 31 |
Beta | 1Q2018 | 01.04.2019 | 4080 | 4896000 | 1028.16 | 10004.16 | monthly | 30 |
Beta | 1Q2018 | 01.05.2019 | 3660 | 4392000 | 922.32 | 8974.32 | monthly | 31 |
Beta | 1Q2018 | 01.06.2019 | 3860 | 4632000 | 972.72 | 9464.72 | monthly | 30 |
Beta | 1Q2018 | 01.07.2019 | 3100 | 3720000 | 781.2 | 7601.2 | monthly | 31 |
Beta | 1Q2018 | 01.08.2019 | 3000 | 3600000 | 756 | 7356 | monthly | 31 |
Beta | 1Q2018 | 01.09.2019 | 3465 | 4158000 | 873.18 | 8496.18 | monthly | 30 |
Beta | 1Q2018 | 01.10.2019 | 3480 | 4176000 | 876.96 | 8532.96 | monthly | 31 |
Beta | 1Q2018 | 01.11.2019 | 3100 | 3720000 | 781.2 | 7601.2 | monthly | 30 |
Beta | 1Q2018 | 01.12.2019 | 3000 | 3600000 | 756 | 7356 | monthly | 31 |
@Anonymous Could you please post the sample data that can be copied.
Proud to be a PBI Community Champion
@Anonymous Please try this as a "New Table"
Test92Out = SUMMARIZE(Test92OilRateAvg,Test92OilRateAvg[Date].[Year],"OilRate",AVERAGE(Test92OilRateAvg[OilRate]),"DateType","Yearly")
Proud to be a PBI Community Champion
@PattemManohar - thank you. Nearly there. Using "average" will give me the wrong value as it weights each month equally. The answer for 2018 should be 4000, not 4003.
The follwoing works; though I still need a nice way to get the number of days in that year into the code instead of just "365" - see highlighted in yellow
User | Count |
---|---|
100 | |
89 | |
81 | |
76 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |