Forum Discussion

Jolyon's avatar
Jolyon
Icon for Helper III rankHelper III
10 years ago
Solved

Calculate actual vs Forecast?

Hi dear community, I am rather new in BI-Field,but have already the first task and questions.   I have two table - with forecast revenue and actual data. The values are given per Item and Month, ...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    Jolyon

     

    In my opinion, you can union those two tables.

     

    unionTable =
UNION (
    SELECTCOLUMNS (
        actual,
        "prodcut", actual[Product],
        "month", actual[Month],
        "value", actual[actual Revenue],
        "product id", actual[Product ID],
        "department", actual[DepartMent],
        "department id", actual[Product ID] & "_"
            & actual[DepartMent],
        "type", "actual"
    ),
    SELECTCOLUMNS (
        forecast,
        "prodcut", forecast[Product],
        "month", forecast[Month],
        "value", forecast[forecast Revenue],
        "product id", forecast[Product ID],
        "department", forecast[DepartMent],
        "department id", forecast[Product ID] & "_"
            & forecast[DepartMent],
        "type", "forecast"
    )
)

    And then feed visuals with the unionTable.

     

    diff = 
var actualrRev = CALCULATE(SUM(unionTable[value]),unionTable[type]="actual")
var forecastRev = CALCULATE(SUM(unionTable[value]),unionTable[type]="forecast")
return forecastRev-actualrRev
Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

Jolyon

 

In my opinion, you can union those two tables.

 

unionTable =
UNION (
    SELECTCOLUMNS (
        actual,
        "prodcut", actual[Product],
        "month", actual[Month],
        "value", actual[actual Revenue],
        "product id", actual[Product ID],
        "department", actual[DepartMent],
        "department id", actual[Product ID] & "_"
            & actual[DepartMent],
        "type", "actual"
    ),
    SELECTCOLUMNS (
        forecast,
        "prodcut", forecast[Product],
        "month", forecast[Month],
        "value", forecast[forecast Revenue],
        "product id", forecast[Product ID],
        "department", forecast[DepartMent],
        "department id", forecast[Product ID] & "_"
            & forecast[DepartMent],
        "type", "forecast"
    )
)

And then feed visuals with the unionTable.

 

diff = 
var actualrRev = CALCULATE(SUM(unionTable[value]),unionTable[type]="actual")
var forecastRev = CALCULATE(SUM(unionTable[value]),unionTable[type]="forecast")
return forecastRev-actualrRev
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
10 years ago

The decision how to transform your data before loading to the data model to work with it further is part of data modelling and this is part science and part art as the aspects to consider can get quite complex. Not easy for beginners - yet a decision you have to take.

 

But as a rule of thumb I'd say that you don't aggregate your data just because your report should output in aggregated form.

This is because the standard-output methods from the data model both in Power BI and Power Pivot will always aggregate by default. So it is not possible to retrieve non-aggregated data from the model. The key to all this lies in the selection of the attributes you drag onto your report pane: If you just drag Product ID for example, everything will be aggregated down to product-level, even the dates will be ignored (as long as no external filters overrule this). So when you then drag more attributes to your report, like date or department, the numbers will be split up further, but still be aggregated, so that just one unique line per attribute combination remains.

 

 As you have experienced already, aggregation of fact tables at data modelling stage makes everything just more complicated.

 

I like the sugestion to create a unified fact table that Eric has made, as it allows you to get started easily without the need to create separate dimension tables with unique values for products and dates and so on. Just keep in mind that for some more advanced calculations you might develop towards more "standardized models" like described in this article for example: http://exceleratorbi.com.au/the-optimal-shape-for-power-pivot-data/

 

As you're new to PowerBI, I'd also highly like to recommend this article as well (actually, I find this the best site for PowerBI beginners anyway, so worth some browsing)  .

 

And one final remark: I think that the query editor is the better place to create the UnionTable: Add a column contain "actual" for your actuals and "forecast" to your forecast table and then append them.