cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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".

pic1.JPG

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

pic2.JPG

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.

pic3.JPG

 

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
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 Screenshot 2017-02-04 14.14.11.png
  • 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

 

Screenshot 2017-02-04 14.11.04.png

 

Sample file and data for reference

 

 

 

View solution in original post

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

Screenshot 2017-02-04 20.51.16.png

View solution in original post

5 REPLIES 5
Highlighted
Memorable Member
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 Screenshot 2017-02-04 14.14.11.png
  • 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

 

Screenshot 2017-02-04 14.11.04.png

 

Sample file and data for reference

 

 

 

View solution in original post

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

Pic2-1.JPG

 

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.

Pic2-2.JPG

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

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

Screenshot 2017-02-04 20.51.16.png

View solution in original post

Highlighted
Frequent Visitor

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

 
Highlighted
Frequent Visitor

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

Thanks so so much, it worked perfectly.

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

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