Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
118 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
104 | |
104 | |
89 | |
66 |