Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
M-P
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
tringuyenminh92
Memorable Member
Memorable Member

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

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
tringuyenminh92
Memorable Member
Memorable Member

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

 

 

 

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

M-P
Frequent Visitor

 
M-P
Frequent Visitor

Thanks so so much, it worked perfectly.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.