calculation of projected volume

[csatary]

Hello,

I would like to calculate the projected volume based on the last years revenues per product category; market and forecasted price.

having the last year revenues on hand we devide it with the last year deliveries and we get the avarage price (per market and product category). we calculate the expected price for the same product category for the next year with the help the price erosion indicator. then we devide the forecasted revenue for next year with this expected avarage prices to get the projected volumes.

I tried to do these calculations with separate measures, but I could not devide the forecasted revenue column (which is in 2017) with the forecasted avarage price (calculated from 2016 values). I think it is because of the data coming from different time periods. so, I cannot distribute data calculated from 2016 year to 2017 year. is my thinking correct? is there any solution for such 'data distribution per different period' problem? can I such calculations within the queries if those data are coming from diferent sources?

Thanks!

Regards,

Ágnes

[Greg_Deckler]

Hello Ágnes, would it be possible to provide sample data and the formulas for your measures/columns. With the information you have provided, my only thought is that you might have to wrap your measure calculation in a CALCULATE with an ALL or ALLEXCEPT or other filter in order to ensure that the context is correct for it's calculation. Not sure what else I can say or speculate on without more information.

[csatary]

Hello smoupre,

My calculation would look like this:

Avarage price(calculated)= Revenue in 2016(given)/delivered units in 2016(given)

Forecast avg price(calculated)=Avarage price(calculated)*price erosion(given)

Forecast volume(calculated) =Forecast Revenue in 2017 (given)/forecast avg price(calculated)

"given" means that data is available from reports for me as a column in excel file and no need to calculate

"Calculated" means that I need to calculate it

and I would put the calculated data into such a table

my problem is that the first 2 calculations are made with values in 2016 time series (Revenue actuals ) and the 3rd calculation would devide a 2017 value (forecast revenue) with the calculated forecast price

[Greg_Deckler]

Without sample source data, I would speculate that you would want to create measures such as:

AveragePrice = SUM([Revenue2016]) / SUM([DeliverUnits2016])

ForecastAvgPrice = [AveragePrice]*SUM([PriceErosion])

ForecastVolume = SUM([ForecastRevenue2017])/[ForecastAvgPrice]

Note that SUM is used as the aggregate but any aggregate of a single value should work, such as MAX, MIN, etc. The aggregate is a requirement for a measure.

[csatary]

smoupre,

Thanks! yes. this is my calculation exactly, but does not work. the 3rd calculation (projected volume) is not giving any value

I assumed that this is because the forecasted avg price is calculated from different data and time period than the revenue forecast

Thanks!

Regards,

Ágnes

[Greg_Deckler]

OK, let's figure out what is going haywire. I just created an Enter Data query with the given values like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSAVJAQs/SHEgagURiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Revenue2016 = _t, DeliverUnits2016 = _t, PriceErosion = _t, ForecastRevenue2017 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Revenue2016", Int64.Type}, {"DeliverUnits2016", Int64.Type}, {"PriceErosion", type number}, {"ForecastRevenue2017", Int64.Type}})
in
    #"Changed Type"

The calculations specified work with this. I take it that you probably have more tables in your source data than a single table with all of your given values. Is your forecast data in a separate table than your 2016 data and can you give me a sense of what tables you have involved?

[csatary]

Hi,

sure

I have 2 tables which are relevant for this calculation:

table 1: revenues 2016 (column 1), revenue forecast (column 2), price erosion(column3) (distributed for market, country, quarter, product)

table 2: volumes (deliveries; plans) (distributed for market, country, quarter, product)

and I have additional  helping tables for both date, customer information and product information linked to each of the above mentioned data tables (this is because of the different hierarchy of the data mentioned in different tables)

regarding the Enter data query you just mentioned - unfortunately I do not understand that 😞

[Greg_Deckler]

For the Enter Data query, you can just create a Blank Query and then click on Advanced Editor. Then you can just copy and paste the code into the Advanced Editor and you will recreate my table.

Unfortunately I am not sure that I am going to be able to solve this without a much better understanding of your source data. I'm not understanding what this might be going wrong. Is the revenue forecast for column 2 in your first table the 2017 forecast that you are trying to use in your last calculation? Can you mock up some data for me in the same format as your source data?

[csatary]

hello,

I am trying to draft my file but could not upload any

so, I am describing below

I have 6 colums:

1. business line

2. product

3. market

4. quarter (in text format, eg 2016 Q1)

5. revenue actuals (this data applies only for 2016, so the rows for 2017 quarters contain no data in this column)

6. revenue forecast (this data applies only for 2017 and on, so the rows for 2016 quarters contain no data in this column)