Power BI – ConfigMgr Device Cost & Renewal Estimator

Managing users and devices is always fun; a major challenge for me was always assigning assets without unnecessarily purchasing or depleting the device stock. An unused or under utilised device in an organisation can potentially be money down the drain. As configMgr admins, we have the data already to determine whats inactive and potentially reusable devices through client reporting and dashboards. Because the business doesn’t always value or fully understand the figures in ConfigMgr. Adding the potential cost can give the business something they can relate too but also allows us to gain an understanding of the value of our estate and ask questions about where and how you potentially use devices.

Another challenge was managing hardware refresh projects, every year going through manually exporting lists and trying to work out which devices needed replacing. This tool is designed to help all those admins out there who struggle with this! Now you can confidently show your management or staff a report that actually puts figures behind the dataset :).

What is the ConfigMgr Device Cost & Renewal Estimator?

The ConfigMgr Device Cost & Renewal Estimator is a free Power BI report which you connect to your ConfigMgr database and using BI visuals, show the calculated potential device cost and the renewal date for refreshing devices. The reports can help with device cost planning and forecasting.

How does it work?

Device Cost Estimator

The Device Cost Estimator gives a really interesting insight into your environment device cost. The report utilizes a calculated creation date from the device information. This date is based on the OS install date and the ConfigMgr creation date, it will by default take the earliest date. The Creation Date is then linked to a financial year calendar; this is calculated by month number and is specified in a parameter. Finally there is a drop down which allows you to specify your estimated average device cost, all the visual calculations are then based from this value.

Cost Estimator

Last Active Cost
The last active visual a completely different way of looking at device utilization. It display device usage by cost. You can even select a category and see who actually was using that device too.

 

Device by Manufacturer
This visual allows you to click through from Manufacturer to Model and more! Just click the arrows on the top of the visual to enable drill down or click through individually.

 

Device Forecast
The Devices by Year visual not only shows you devices created by year to date but also a predicted device count for the next 5 years based on the total history. This allows you to estimate the potential device increase.

Device Renewal Cycle Estimator

The Device Renewal Cycle Estimator is really cool! Based on a parameter for your estimated device recycle period (in years), it will show you which devices need renewing and indicate any potential cost. This allows you to map out when you need to replace devices and plan ahead. With the Manufacturer and Model matrix, you can also see what device type actually needs replacing.

Renewal Cycle

 

With the Devices by Renewal Status visual, you can easily filter devices by there renewal status. For example, if I wanted to see which devices are currently past their renewal, I could filter by requires replacing which would show me exactly needs replacing.

 

Est Devices Renewal Cost
With the Estimated Devices Renewal Cost and Devices by Year (which is a date drill down), it displays an estimated renewal count for each renewal year giving you visibility over how many devices you may need to replace.

 

 

How to Configure the Report

Prerequisites

Setup

  1. Download the latest Power BI Desktop
  2. Before attempting to open the template file, open Power BI Desktop first
  3. Go to File > Options and Settings > Options > Security
  4. Under Native Database Queries, untick Require user approval for new native SQL queries
  5. Close the open Power BI desktop and open the template file
  6. Enter the SQL server or instance name where the Configuration Manager site database is located
  7. Enter the SQL database name for the Configuration Manager site
  8. Supply the Finance End Month number, for example entering 3 would be March as the year end month
  9. Drop the Renewal Cycle (Years) and select your renewal period for swapping devices.

 

Parameters page

Changing the Currency

The currency by default is set to GBP (£), to change the currency:

  • Go the Fields\Cost table on the right panel.

Currency Settings

  • Select the “Currency Symbol” to whatever your currency is (This is only used for visual text formatting).

Currency change

  • For all the other measures (The fields with a calculator symbol next to it), click each measure individually. Finally, go to the modelling tab and drop the $ drop-down to select your currency.

Currency Model

 

DOWNLOAD

The template is downloadable from the Technet Gallery 🙂

DOWNLOAD TEMPLATE

 

Finally, I hope you enjoying using this report. If you have any feedback or suggestions comment below as would love to hear them.

Cheers,

Raf

Tagged with:

Leave a Reply

Your email address will not be published. Required fields are marked *