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

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's avatar
roma
Icon for Advocate II rankAdvocate II
6 years ago

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.