Actual vs Forecast in Aggregated sums

zakkair · Mar 6, 2013

Hi,

I have an Excel file that currently compares the Actual data coming in vs the Forecast.

The forecast is given in months (example: Volume in Calgary is 200,000L for the month of January).

The actual data coming in is in days (example: volume for calgary on January 1st is 4,500L)

I want to make an aggregated line chart that compares the two. So for forecast, it starts a 0L and goes up by the daily volume, while the forecast is a fixed straight line going up. How would I do this in DAX?

Thanks in advance for the help. I greatly appreciate it

Sample doc:

Actual Acquired
[TABLE="width: 772"]
<TBODY>[TR]
[TD]Product 2
[/TD]
[TD]Contract
[/TD]
[TD]Region
[/TD]
[TD]Business Group
[/TD]
[TD]Supply Orbit
[/TD]
[TD]Supply Region
[/TD]
[TD]Supplier
[/TD]
[TD]Product
[/TD]
[TD]Brand
[/TD]
[TD]Terminal
[/TD]
[TD]Load Date
[/TD]
[TD]Volume
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/9/2013
[/TD]
[TD="align: right"]7139
[/TD]
[/TR]
[TR]
[TD]ULSD #1
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/10/2013
[/TD]
[TD="align: right"]2016
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/10/2013
[/TD]
[TD="align: right"]3011
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/11/2013
[/TD]
[TD="align: right"]4524
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/11/2013
[/TD]
[TD="align: right"]5736
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/11/2013
[/TD]
[TD="align: right"]6027
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/11/2013
[/TD]
[TD="align: right"]6027
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/11/2013
[/TD]
[TD="align: right"]6032
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/17/2013
[/TD]
[TD="align: right"]7036
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/17/2013
[/TD]
[TD="align: right"]10576
[/TD]
[/TR]
[TR]
[TD]ULSD #1
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/18/2013
[/TD]
[TD="align: right"]1414
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/18/2013
[/TD]
[TD="align: right"]7034
[/TD]
[/TR]
[TR]
[TD]Premium gas
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]PRM
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/21/2013
[/TD]
[TD="align: right"]1010
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/21/2013
[/TD]
[TD="align: right"]3523
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/22/2013
[/TD]
[TD="align: right"]2215
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/22/2013
[/TD]
[TD="align: right"]11072
[/TD]
[/TR]
[TR]
[TD]ULSD
[/TD]
[TD]Joe - Gas
[/TD]
[TD]West
[/TD]
[TD]WHOLESALE
[/TD]
[TD]Burnaby
[/TD]
[TD]Pacific
[/TD]
[TD]Joe
[/TD]
[TD]DIS
[/TD]
[TD]Unbranded
[/TD]
[TD="align: right"]10227
[/TD]
[TD="align: right"]1/22/2013
[/TD]
[TD="align: right"]3827
[/TD]
[/TR]
</TBODY>[/TABLE]

Forecast
[TABLE="width: 1499"]
<TBODY>[TR]
[TD]Supplier[/TD]
[TD]Contract[/TD]
[TD]Region2[/TD]
[TD]Orbit[/TD]
[TD]Brand[/TD]
[TD]Lift Point[/TD]
[TD]Product Class[/TD]
[TD]Product[/TD]
[TD]Product Detail[/TD]
[TD] Jan 2013 [/TD]
[TD] Feb 2013 [/TD]
[TD] Mar 2013 [/TD]
[TD] Apr 2013 [/TD]
[TD] May 2013 [/TD]
[TD] Jun 2013 [/TD]
[TD] Jul 2013 [/TD]
[TD] Aug 2013 [/TD]
[TD] Sep 2013 [/TD]
[TD] Oct 2013 [/TD]
[TD] Nov 2013 [/TD]
[TD] Dec 2013 [/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Joe - Gas[/TD]
[TD]West[/TD]
[TD]Pacific[/TD]
[TD]Unbranded[/TD]
[TD]Burnaby[/TD]
[TD]Gas[/TD]
[TD]Premium gas[/TD]
[TD]Premium gas[/TD]
[TD] 200,000 [/TD]
[TD] 300,000 [/TD]
[TD] 300,000 [/TD]
[TD] 300,000 [/TD]
[TD] 400,000 [/TD]
[TD] 400,000 [/TD]
[TD] 400,000 [/TD]
[TD] 400,000 [/TD]
[TD] 400,000 [/TD]
[TD] 300,000 [/TD]
[TD] 300,000 [/TD]
[TD] 300,000 [/TD]
[TD]########[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Joe - Gas[/TD]
[TD]West[/TD]
[TD]Pacific[/TD]
[TD]Unbranded[/TD]
[TD]Burnaby[/TD]
[TD]Gas[/TD]
[TD]E10[/TD]
[TD]E10[/TD]
[TD] 2,200,000 [/TD]
[TD] 1,700,000 [/TD]
[TD] 1,700,000 [/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD] 1,700,000 [/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Joe - Gas[/TD]
[TD]West[/TD]
[TD]Pacific[/TD]
[TD]Unbranded[/TD]
[TD]Burnaby[/TD]
[TD]Distillate[/TD]
[TD]ULSD[/TD]
[TD]ULSD[/TD]
[TD] 2,000,000 [/TD]
[TD] 2,000,000 [/TD]
[TD] 2,000,000 [/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD] 2,000,000 [/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[TD]########[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Joe - Gas[/TD]
[TD]West[/TD]
[TD]Pacific[/TD]
[TD]Unbranded[/TD]
[TD]Burnaby[/TD]
[TD]Distillate[/TD]
[TD]ULSD[/TD]
[TD]ULSD #1[/TD]
[TD] 200,000 [/TD]
[TD] 150,000 [/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]200000[/TD]
[TD="align: right"]125000[/TD]
[TD="align: right"]1075000[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=4><COL span=4><COL span=2><COL><COL span=7></COLGROUP>[/TABLE]

powerpivotpro · Mar 6, 2013

Have you seen these posts?

Sales/Budget: Integrating Data of Different “Grains” « PowerPivotPro
Data of Different Grains: A Followup « PowerPivotPro

Jacob Barnett · Mar 6, 2013

There's also this incredible technique from Marco Russo:

Budget and Other Data at Different Granularities in PowerPivot | SQLBI

The beauty of this approach is that you don't need relationships or additional tables to make it work AND it's a pattern that you can reuse continually once you get your head round the idea (which isn't totally straightforward).

zakkair · Mar 7, 2013

I have already created a Actual vs Forecast in that way but it is the total comparison of each month. I want to make an aggregated chart to compare daily volume.

Right now, I'm looking to split the forecast into individual days. Example: Jan 2013 forecast is 300,000, so I want to split that off into
Jan 1: 300000/31*1, Jan 2: 300000/31*2, Jan 3: 300,000/31*3.... to make an aggregate line chart for forecast.

This way, I can compare it to my daily actuals, Jan 1, we got 280,000, Jan 2 would be 280000(jan 1 data) + Jan 2 data.

Is this possible using formulas on DAX based on the format of my 2 tables or do I have to manually create the tables. I want to be able to filter it with slicers so I can get daily comparisions for different regions.

zakkair · Mar 7, 2013

I have created a totalMTD for lift actuals --> totalmtd(sum('Lift Actuals'[Volume]), 'Lift Actuals'[Load Date])

I also created a measure that divide my budget january by 31. How do I make Measure 2 also aggreggate?
Similar to this:
[TABLE="width: 519"]
<TBODY>[TR]
[TD]Row Labels
[/TD]
[TD]Sum of Volume
[/TD]
[TD][/TD]
[TD]MTD Sum Lift Actual
[/TD]
[TD]Measure 2
[/TD]
[/TR]
[TR]
[TD]1/1/2013
[/TD]
[TD="align: right"]5,115,814
[/TD]
[TD="align: right"][/TD]
[TD]5,115,814 
[/TD]
[TD="align: right"]11.70967742
[/TD]
[/TR]
[TR]
[TD]1/2/2013
[/TD]
[TD="align: right"]13,207,893
[/TD]
[TD="align: right"][/TD]
[TD]18,323,706 
[/TD]
[TD="align: right"]11.70967742
[/TD]
[/TR]
[TR]
[TD]1/3/2013
[/TD]
[TD="align: right"]11,507,828
[/TD]
[TD="align: right"][/TD]
[TD]29,831,534 
[/TD]
[TD="align: right"]11.70967742
[/TD]
[/TR]
[TR]
[TD]1/4/2013
[/TD]
[TD="align: right"]11,787,756
[/TD]
[TD="align: right"][/TD]
[TD]41,619,290 
[/TD]
[TD="align: right"]11.70967742
[/TD]
[/TR]
[TR]
[TD]1/5/2013
[/TD]
[TD="align: right"]9,661,009
[/TD]
[TD="align: right"][/TD]
[TD]51,280,299 
[/TD]
[TD="align: right"]11.70967742
[/TD]
[/TR]
</TBODY>[/TABLE]

marco.russo · Mar 8, 2013

Maybe you want to write this:

Code:

M2YTD := SUMX( DATESYTD( 'Lift Actuals'[Load Date] ), [Measure 2] )

zakkair · Mar 8, 2013

Hi Marco, it works really good for the annual data. Thanks a lot :D

How would I fix the formula for it to be split off into months. For example, right now when I filter the month-year to only show February 2013 data, the M2YTD starts off at the total of January 2013. How would I change the measure to make it start off at February 2013. My MTD Sum Lift Actual starts over from the beginning of Feb when I filter it. I was hoping the M2YTD would too.

Nevermind! I went around it and did this:
sumx(Datesytd('Lift Actuals'[Load Date]), [BU Forecast Daily Feb])-31*[bu forecast daily feb]

for another pivot table underneath and filtered it to February month only so I can compare the monthly data. It seems to work, just need to write a new measure for every month.

Thanks a lot! If there's another way to make it easier, I'll be so thankful!

marco.russo · Mar 8, 2013

If you want a running total (which is not a YTD, but a running total of visible data in the PivotTable), use this:

Code:

M2YTD := SUMX( DATESYTD( ALLSELECTED( 'Lift Actuals'[Load Date] ) ), [Measure 2] )

Actual vs Forecast in Aggregated sums

zakkair

New Member

Excel Facts

powerpivotpro

Board Regular

Jacob Barnett

Board Regular

zakkair

New Member

zakkair

New Member

marco.russo

New Member

zakkair

New Member

marco.russo

New Member

Forum statistics

Share this page

Actual vs Forecast in Aggregated sums

New Member

Excel Facts

Board Regular

Board Regular

New Member

New Member

New Member

New Member

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock