Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Anonymous
Not applicable

Calculating yearly average rate from monthly average production rate

‎11-07-2018 02:49 AM

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

 

 

Capture1.PNGCapture2.PNG

Labels:
Message 1 of 8
2,501 Views
0
7 REPLIES 7
v-yulgu-msft
Employee
Employee

‎11-07-2018 10:03 PM

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" )

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Message 6 of 8
2,459 Views
0
Anonymous
Not applicable
In response to v-yulgu-msft

‎11-08-2018 12:48 AM

@v-yulgu-msft 

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:

Capture4.PNG

Message 7 of 8
2,447 Views
0
Anonymous
Not applicable
In response to Anonymous

‎11-08-2018 12:49 AM

Sample data

 

SubprojectBudgetDateOil rate (m3/d)Gas rate (m3 gas/d)NGL (m3/d)BOE (m3/d)DateTypeNoDays
Alpha2Q201801.01.201847509500000199516245monthly31
Alpha2Q201801.02.2018456091200001915.215595.2monthly28
Alpha2Q201801.03.201843508700000182714877monthly31
Alpha2Q201801.04.201842008400000176414364monthly30
Alpha2Q201801.05.201840008000000168013680monthly31
Alpha2Q201801.06.2018367073400001541.412551.4monthly30
Alpha2Q201801.07.2018381076200001600.213030.2monthly31
Alpha2Q201801.08.201835007000000147011970monthly31
Alpha2Q201801.09.2018423084600001776.614466.6monthly30
Alpha2Q201801.10.2018377075400001583.412893.4monthly31
Alpha2Q201801.11.201834006800000142811628monthly30
Alpha2Q201801.12.201838007600000159612996monthly31
Beta2Q201801.01.201939604752000997.929709.92monthly31
Beta2Q201801.02.2019411049320001035.7210077.72monthly28
Beta2Q201801.03.2019415049800001045.810175.8monthly31
Beta2Q201801.04.2019398047760001002.969758.96monthly30
Beta2Q201801.05.201935604272000897.128729.12monthly31
Beta2Q201801.06.201937604512000947.529219.52monthly30
Beta2Q201801.07.2019300036000007567356monthly31
Beta2Q201801.08.201929003480000730.87110.8monthly31
Beta2Q201801.09.201933654038000847.988250.98monthly30
Beta2Q201801.10.201933804056000851.768287.76monthly31
Beta2Q201801.11.2019300036000007567356monthly30
Beta2Q201801.12.201929003480000730.87110.8monthly31
Alpha1Q201801.01.201848009600000201616416monthly31
Alpha1Q201801.02.2018461092200001936.215766.2monthly28
Alpha1Q201801.03.201844008800000184815048monthly31
Alpha1Q201801.04.201842508500000178514535monthly30
Alpha1Q201801.05.201840508100000170113851monthly31
Alpha1Q201801.06.2018372074400001562.412722.4monthly30
Alpha1Q201801.07.2018386077200001621.213201.2monthly31
Alpha1Q201801.08.201835507100000149112141monthly31
Alpha1Q201801.09.2018428085600001797.614637.6monthly30
Alpha1Q201801.10.2018382076400001604.413064.4monthly31
Alpha1Q201801.11.201834506900000144911799monthly30
Alpha1Q201801.12.201838507700000161713167monthly31
Beta1Q201801.01.2019406048720001023.129955.12monthly31
Beta1Q201801.02.2019421050520001060.9210322.92monthly28
Beta1Q201801.03.201942505100000107110421monthly31
Beta1Q201801.04.2019408048960001028.1610004.16monthly30
Beta1Q201801.05.201936604392000922.328974.32monthly31
Beta1Q201801.06.201938604632000972.729464.72monthly30
Beta1Q201801.07.201931003720000781.27601.2monthly31
Beta1Q201801.08.2019300036000007567356monthly31
Beta1Q201801.09.201934654158000873.188496.18monthly30
Beta1Q201801.10.201934804176000876.968532.96monthly31
Beta1Q201801.11.201931003720000781.27601.2monthly30
Beta1Q201801.12.2019300036000007567356monthly31
Message 8 of 8
2,446 Views
0
PattemManohar
Community Champion
Community Champion

‎11-07-2018 03:03 AM

@Anonymous Could you please post the sample data that can be copied.





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Message 2 of 8
2,496 Views
0
Anonymous
Not applicable
In response to PattemManohar

‎11-07-2018 03:48 AM
Data Sample Date Oil rate (m3/d) DateType NoDays 01.01.2018 4750 monthly 31 01.02.2018 4560 monthly 28 01.03.2018 4350 monthly 31 01.04.2018 4200 monthly 30 01.05.2018 4000 monthly 31 01.06.2018 3670 monthly 30 01.07.2018 3810 monthly 31 01.08.2018 3500 monthly 31 01.09.2018 4230 monthly 30 01.10.2018 3770 monthly 31 01.11.2018 3400 monthly 30 01.12.2018 3800 monthly 31 01.01.2019 3960 monthly 31 01.02.2019 4110 monthly 28 01.03.2019 4150 monthly 31 01.04.2019 3980 monthly 30 01.05.2019 3560 monthly 31 01.06.2019 3760 monthly 30 01.07.2019 3000 monthly 31 01.08.2019 2900 monthly 31 01.09.2019 3365 monthly 30 01.10.2019 3380 monthly 31 01.11.2019 3000 monthly 30 01.12.2019 2900 monthly 31 01.01.2020 2500 yearly 366 01.01.2021 2000 yearly 365
Message 3 of 8
2,484 Views
0
PattemManohar
Community Champion
Community Champion
In response to Anonymous

‎11-07-2018 07:09 AM

@Anonymous Please try this as a "New Table"

 

Test92Out = SUMMARIZE(Test92OilRateAvg,Test92OilRateAvg[Date].[Year],"OilRate",AVERAGE(Test92OilRateAvg[OilRate]),"DateType","Yearly")

image.png





Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion




Message 4 of 8
2,469 Views
0
Anonymous
Not applicable
In response to PattemManohar

‎11-08-2018 12:33 AM

@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

Capture3.PNG

 

Message 5 of 8
2,450 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All