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
BI_Learner's avatar
BI_Learner
Frequent Visitor
8 years ago

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!