One of my visualisation is a boring P&L in a Table format, so I have the following columns with revenue and cost categories going down:
Actuals / Budget / Forecast / Last Year
Revenue
Expenses
Operating Income
Now I would like to add comparison columns that calculates the diffrence in a number and a %, so like this:
Actuals / Budget / ACTUALS VS BUDGET/ Forecast / ACTUALS VS FORECAST/ Last Year / ACTUALS VS LAST YEAR
Revenue
Expenses
Operating Income
Is it possible to create the calculations in the visualisation? Or do I have to have underlying comparison columns in the data set?
Kind regards,
Vladimir
Hi Vladimir,
You need to write measures for these calculations. See the Power Pivot course session 9.02 for the Calculate function.
Note: I recommend you complete the Power Query and Power Pivot courses prior to tackling Power BI as these tools are the foundation of Power BI. If you set up your model wrong you'll have no end of hassles.
Mynda
Thank you!
Absolutely love the course! While I work with my file I can always go back to the videos and find a great solution. One of the problems I had was my data labels such as Revenue, Income, COGS etc all where under one column called Reporting Label, but with the following DAX measure you can filter out data and create a measure within a column against different labels such as Operating Margin %.
SUMX(FILTER(Actual;Actual[REPORTINGLABEL]="Operating Income");Actual[Actuals EUR])/SUMX(FILTER(Actual;Actual[REPORTINGLABEL]= "Revenue");Actual[Actuals EUR]) Maybe this will help others! Kind regards, Vladi
You're welcome, Vladimir. Glad you're enjoying it and thanks for sharing your tip.
Mynda
