Help with adding new custom columns in table in report view

[M-P]

Hello, I need help with formula to add 2 new custom columns in the table in report view to calculate the "Var" =Forecast - Budget,  and Ach = Actual/Budget as shown below.

First: Below is the screen shot of my simple table, note: under the Scenario column, I have 3 cases, "Actual", "Budget" and "Forecast".

 I then had created a matrix table in Power BI as shown below

Below is the help I need:

         I need to add 2 more columns to the above table,

• 1st column is to calculate the Variance which is the Forecast amount - the Budget amount,
• and the 2nd column is to calculate the Achieved percentage which is the Actual amount / the Budget amount.

Below is the screen shot of the pivot table that I had setup in Excel, but I do not know how to do the same in Power BI.

I hope to get help with step by step instructions how to add these 2 columns with the correct formula to accomplish the above task. Thanks in advance for your help.

M-P

[tringuyenminh92 · Accepted solution]

Hi @M-P,

There are some approaches that could achieve your expectation, i will show you one of them by creating 5 calculated measures(forecast, budget, actual, variance and % of actual/budget).

• Right click in your table name, choose new measure 
• Create Actual measure:

Actual = CALCULATE( SUM(Data[Amount]),Data[Scenario]="Actual")

• Create Budget measure:

Budget = CALCULATE( SUM(Data[Amount]),Data[Scenario]="Budget")

• Create Forecast measure:

Forecast = CALCULATE( SUM(Data[Amount]),Data[Scenario]="Forecast")

• Create Variance measure by reuse 2 measures above:

Variance = [Forecast]-[Budget]

• Create % budget/actual measure

% Actual amount / the Budget amount = DIVIDE( [Actual],[Budget])

Choose matrix control and put these measures to Values area of matrix

Sample file and data for reference

[tringuyenminh92 · Accepted solution]

Hi @M-P,

There are some approaches that could achieve your expectation, i will show you one of them by creating 5 calculated measures(forecast, budget, actual, variance and % of actual/budget).

• Right click in your table name, choose new measure 
• Create Actual measure:

Actual = CALCULATE( SUM(Data[Amount]),Data[Scenario]="Actual")

• Create Budget measure:

Budget = CALCULATE( SUM(Data[Amount]),Data[Scenario]="Budget")

• Create Forecast measure:

Forecast = CALCULATE( SUM(Data[Amount]),Data[Scenario]="Forecast")

• Create Variance measure by reuse 2 measures above:

Variance = [Forecast]-[Budget]

• Create % budget/actual measure

% Actual amount / the Budget amount = DIVIDE( [Actual],[Budget])

Choose matrix control and put these measures to Values area of matrix

Sample file and data for reference

[M-P]

Hello Tringuyenminh92, Thanks so much for the quick response and the perfect step by step guidance, the above approach works beautifully with the original data that had presented above.

Sorry for more stupid questions, when I added two coumns, 1st one is for the "Customer" and the 2nd one is "Category" as shown below

The new table (table 2) with your awesome calculated measure column did not produce the same result in term of the math as the orginal matrix table (Table 1) as shown below.

I just barely started to use Power BI this week and hence many basic questions. Thanks in advance for the help

[M-P]

Thanks so so much, it worked perfectly.

[tringuyenminh92 · Accepted solution]

Hi @M-P,

Just replace Amount field by drag&drop Actual measure to Values area. I forgot that when saving. It's fine to me for any concern, hope you love PBI and DAX ^_^