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, 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!

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

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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's avatar
      Jolyon
      Icon for Helper III rankHelper III
      10 years ago

      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'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