Calculate actual vs Forecast?

[Jolyon]

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, and there can be several records per Month.

 

As a first sub-task I grouped the forecast revenues by Item and Month (thanks to community=) from the first table

and got such a table.

 

1. And here is my first question: when I make a "GROUP BY" step from the original Forecast table, can I save it as a separate table? If i do it in the original table, I lose other important data like Prod-ID and Department.
2. Is it then possible to produce a relationship between these two tables - original and new?

As a second sub-task I need to compare Actual vs. Forecast values per Month and Item and visualize the differences.

For this purpose I wanted to connect two tables(Forecast and Actual) per Prod-ID as a primary key, but faced the problem: i cannot make a relation between them and get an alert "Relation between the tables is not possible, at least one column in the table should contain unique data".

I suppose the cause of the problem is, that I have several records per Item in my table(for exaple Item 1-ProdID1-Month1,Item1-ProdID1-Month2,Month3 etc.)

What can I do in that case and how can I calculate the difference? Should I make a new measure or column?

Thank you all!

[Greg_Deckler]

You need some additional queries. For the group by one, just create another query that does the group by and loads it into a separate table. Then, you can build a relationship between it and your other tables.

For your Prod-ID issue relating Forecast and Actual tables, just create another query that only pulls the Prod-ID column and then filters it to unique values. This can all be done in the ribbon in the Query Editor. Then, relate both your Forecast and Actual tables to this new single-column table with distinct Prod-ID's and Bob's your uncle.

[Jolyon]

Hi, @Greg_Deckler,

thanks for the answer. That's the problem,  how  can I load the GROUP BY query from original table to a separate table?or do you mean, i should load the original dataset once more and only then make the Group By query?

At the moment when I group the values in the original table, I lose some other important columns of values.

OR: if I create a new table, copy there there the first query and then make some data manipulation in the new table, all new changes  affect also the first original table(which i don't want to)..

2) and another question, if you could help: how can I calculate the Actual<->Forecast difference with DAX-query?(one column with the absolute numbers and another column - with percent, i.e.(Actual-Forecast)/Forecast).
Say, if i had such two tables:

thanks a lot!

[Eric_Zhang · Accepted solution]

@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

[BI_Learner]

Hi Eric_Zhang,

I have similar data tables as Jolyon's. The the differences are, using Jolyon's data sample, I have more items (item 4, 5...) in the Product column and more months (Sep, Oct...) in the Month column for the forecast table. And let's assume my actual table is the same as Jolyon's table. Would your solution work? I tried a similar method before, and it didn't work for me.

Thank you in advance!

[Jolyon]

Hi @Eric_Zhang
thank you for the answer!

I've made like you said a Union Table and a column with  Difference Values, but got in Diff column the same results as in the Values column:

For Diff Column I have written formula:

Diff = CALCULATE(sum(unionTable[value]);unionTable[type]="actual")- CALCULATE(sum(unionTable[value]);unionTable[type]="forecast")

How can I define, that power BI should compare actual vs. forecast row for row: item1-June; item 1-July, item 1-August and so on?

Do I need some further table transformations?

as always, many thanks

[roma]

Hi Jolyon,

Thanks for the solution.

Actually I have the related problem where I have to calculate Actual % formula is Actual KPI/sum(sales_Actual) likewise for the budget also. Can you please help me with the solution.

As in considering your example =calculate(sum(value),filter(type="actual")/calculate(sum(value),filter(department="sales",type="actual") is not giving me the corret answer.

[Jolyon]

I think, I've understood my error.
I should have calculate the difference as a Measure, not as a calculated column, right?

only that way I get correct results..

[ImkeF]

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.