cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Help with adding new custom columns in table in report view

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted Memorable Member

## Re: Help with adding new custom columns in table in report view

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

Highlighted Memorable Member

## Re: Help with adding new custom columns in table in report view

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 ^_^ 5 REPLIES 5
Highlighted Memorable Member

## Re: Help with adding new custom columns in table in report view

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

Highlighted
Frequent Visitor

## Re: Help with adding new custom columns in table in report view

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

Highlighted Memorable Member

## Re: Help with adding new custom columns in table in report view

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 ^_^ Highlighted
Frequent Visitor

Highlighted
Frequent Visitor

## Re: Help with adding new custom columns in table in report view

Thanks so so much, it worked perfectly.

Announcements #### Get Ready for Power BI Dev Camp #### Power Platform Community Conference

Check out the on demand sessions that are available now! #### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021 #### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más! Top Solution Authors
Top Kudoed Authors
Users online (1,157)