Ingredients:
- Financial Analysis (or any type of variance report will do)
- Icon()
- Assorted Formula Columns
Assumptions:
This isn’t a recipe, not really, more of a seasoning. A large grain of finishing salt that goes on top of the main course for flavor right at the end! For this, you’ll want a report comparing two metrics (Cost vs Forecast, Cost vs Budget, Current Cost vs Prior Period) anything of your choosing.
Steps:
- Check-out the financial analysis report you will be working with
- Add a table to your report
- Drag a dimension of your choice into this table (Account, Cost Center, Vendor, Tower, Application) whatever works for your granularity (salt pun intended) and location in the model
- Next, sprinkle in those metrics – I’m using Cost and Budget
- Insert a formula column
- With report table selected->Data Ribbon ->Insert->Formula Column
- Name it Variance =Budget-Cost
- Insert a formula column
- With report table selected->Data Ribbon ->Insert->Formula Column
- Name it Abs Variance =ABS(Variance)
- Insert a formula column
- With report table selected->Data Ribbon ->Insert->Formula Column
- Name it Circles =icon("3colorcircles",Abs Variance<10000, Abs Variance>10000 AND Abs Variance<20000, Abs Variance>20000)*
- Icon Function Here
- Hide Abs Variance column
- In the Ad Hoc Component Configuration of the report, right click on Abs Variance and select hide
Footnote:
The arguments in the icon formulas should contain values that are relevant to your business, IT, and finance teams. Chat with your stakeholders to determine what the threshold should be in your situation.
Footnote Extra:
Combine this icon() tool with an editable table, and you can capture user comments for the line items that have red circles. Comment below if you’d like that recipe next.