Forum Discussion
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
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 ^_^
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
Memorable MemberHi 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 ^_^