How to setup Power BI Publish new Power BI reports based on FinOps toolkit starter kits, extend them to include business context, integrate cost data into your existing reports, or migrate from older Cost Management solutions.

Download How to setup

On this page

The FinOps toolkit Power BI reports provide a great starting point for your FinOps reporting. We recommend customizing them to keep what works, edit and augment reports with your own data, and remove anything that isn’t needed. You can also copy and paste visuals between reports to create your own custom reports.

If you’re just getting started and have less than $2-5 million to report on, start with the Cost Management connector report that leverages the Microsoft Cost Management connector for Power BI. If you run into performance issues or need to report on more than $5 million, use FinOps hubs with the other available reports and configure incremental refresh. The main difference between the two is that the connector is built into Power BI and requires no external setup, while FinOps hubs requires resources to be deployed to your subscription, which enable additional capabilities. Most organizations can use the connector, depending on the reporting requirements. FinOps hubs is generally for more advanced scenarios. For details, see Help me choose.

Use the guides below to connect and customize FinOps toolkit and other Power BI reports.


➕ Setup your first report

The FinOps toolkit Power BI reports include pre-configured visuals, but are not connected to your data. Use the following steps to connect them to your data:

  1. Download and open the desired report in Power BI Desktop.

  2. Select the Transform data button in the toolbar.

    Screenshot of the Transform data button in the Power BI Desktop toolbar.

  1. If connecting to a FinOps hub instance, set the following properties:

    • Storage URL is the URL of your hub storage account. Copy this value from the portal:
      1. Open the list of resource groups in the Azure portal.
      2. Select the hub resource group.
      3. Select Deployments in the menu.
      4. Select the hub deployment.
      5. Select Outputs.
      6. Copy the value for storageUrlForPowerBI.
    • If you customized the deployment to use compressed CSV instead of Parquet, change FileType to .gz. Most people will not change this.
    • Change RangeStart and RangeEnd to the desired start/end dates for your report. The default is the current calendar year. Consider using your fiscal year.

      Enable incremental refresh to load more than $5M of raw cost details. Power BI reports can only support $2-5M of data when incremental refresh is not enabled. After incremental refresh is enabled, they can support $2-5M/month for a total of ~$65M in raw cost details.

    • CM connector settings are required for any reports that rely on data not supported in FinOps hubs yet (i.e., actual costs, reservation recommendations). Be sure to also supply those settings in the next section.
    • Actual costs are included by default when the connector details are specified. If you do not want to include actual cost data from the Cost Management connector, open the CostDetails query in the advanced editor and change the 2 to a 1. This will avoid calling the Cost Management connector.

    Screenshot of instructions to connect to a FinOps hub

  2. If using the Cost Management connector report or Commitment discounts report, set the following properties in the 🛠️ Setup > CM connector folder:

    • Scope is your EA enrollment number or MCA scope ID.
      • A “scope ID” is a fully-qualified Azure resource ID for the MCA billing account or billing profile you want to connect to.
      • If using the Cost Management connector report, you can connect to an MCA billing account to view cost across all billing profiles but reservation recommendations will not be available in the Coverage pages.
      • If using the Commitment discounts report, you must use a billing profile since the connector is used for reservation recommendations which are only available for billing profiles.
      • An MCA billing account scope ID looks like /providers/Microsoft.Billing/billingAccounts/{billingAccountId}.
      • An MCA billing profile scope ID looks like /providers/Microsoft.Billing/billingAccounts/{billingAccountId}/billingProfiles/{billingProfileId}.
      • You can get the billing account and profile IDs from the Azure portal:
        • Go to https://aka.ms/costmgmt/config.
        • Change the scope to the desired MCA billing profile or EA billing account.
        • Select the Properties tile.
        • Copy the ID or Billing account ID values.
    • Type is your MCA billing profile ID.

      The billing profile ID is optional for cost reports, but is required for reservation recommendations. When not specified, cost reports will include all billing profiles within the account.

    • Number of Months is the number of months of data to include in the report.

      The Cost Management connector does not support incremental refresh and Power BI reports can only support ~$2-5M of data when incremental refresh is not enabled. You may need to adjust the number of months in your report to fit within this limit.

    Screenshot of instructions to connect to the Cost Management connector

  3. Select the Close & Apply to save your settings.

If you run into any issues syncing your data, see Troubleshooting Power BI reports.


📋 Copy queries from a toolkit report

FinOps toolkit reports manipulate the raw data to facilitate specific types of reports. If you need to connect your data to a new or existing Power BI report that doesn’t currently use FinOps toolkit or Cost Management data source, the best option is to copy queries, columns, and measures from a FinOps toolkit report.

  1. Download one of the FinOps toolkit reports.
  2. Open the report in Power BI Desktop.
  3. Select Transform data in the toolbar.
  4. In the Queries list on the left, right-click CostDetails (or other query) and select Copy.
  5. Open your report in Power BI Desktop.
  6. Select Transform data in the toolbar.
  7. Right-click the empty space in the bottom of the Queries pane and select New group….
  8. Set the name to FinOps toolkit and select OK.
  9. Right-click the FinOps toolkit folder and select Paste.
  10. Select Close & Apply in the toolbar for both reports.

At this point, you have the core data from the FinOps toolkit reports, extended to support Azure Hybrid Benefit and FOCUS reports. In addition to these, you may also be interested in the custom columns and measures that summarize savings, utilization, cost over time, and more. Unfortunately, Power BI doesn’t provide a simple way to copy columns and measures. Perform the following for each column and measure you’d like to copy:

  1. In the FinOps toolkit report, expand the CostDetails (or other table) table in the Data pane on the right.
  2. Select a custom column or measure, then copy the formula from the editor at the top of the window, under the toolbar.

    Be sure to make note if this is a column or a measure. Columns have a table icon with a “Σ” or “fx” symbol and measures have a calculator icon.
    Screenshot of the calculated column and measure icons in Power BI

  3. In your report, right click the CostDetails table and select New measure or New column based on what you just copied.
  4. When the formula editor is shown, paste the formula using Ctrl+V or Cmd+V.
  5. Repeat steps 2-4 for each desired column and measure.

Note that some columns and measures depend on one another. You can ignore these errors as you copy each formula. Each will resolve itself when the dependent column or measure is added.

For details about the columns available in Power BI, refer to the data dictionary.


🛠️ Connect manually

If you don’t need any of the custom columns and measures provided by the FinOps toolkit reports, you can also connect directly to your data using one of the built-in Power BI connectors.

If using the Cost Management connector, refer to Create visuals and reports with the Cost Management connector.

If using FinOps hubs, you’ll use the Azure Data Lake Storage Gen2 connector:

  1. Open your desired report in Power BI Desktop.
  2. Select Get data in the toolbar.
  3. Search for lake and select Azure Data Lake Storage Gen2
  4. Set the URL using deployment outputs:
    1. Open the list of resource groups in the Azure portal.
    2. Select the hub resource group.
    3. Select Deployments in the menu.
    4. Select the hub deployment.
    5. Select Outputs.
    6. Copy the value for storageUrlForPowerBI.
  5. Select the OK button.

    If you receive an “Access to the resource is forbidden” error, grant the account loading data in Power BI the Storage Blob Data Reader role.

  6. Select the Combine button.
  7. Select the OK button.

For more details about connecting to Azure Data Lake Storage Gen2, see Connect to ADLS Gen2. For details about the columns available in storage, refer to the data dictionary.


🚚 Migrate from the Cost Management template app

The Cost Management template app does not support customization in Power BI Desktop and is only supported for Enterprise Agreement (EA) accounts. We recommend starting from one of the FinOps toolkit reports that work across account types rather than customizing the template app. If would like to customize or copy something from the template, see Cost Management template app.


🏗️ Migrate from the Cost Management connector

The Cost Management connector provides separate queries for actual (billed) and amortized costs. In an effort to minimize data size and improve performance, the FinOps toolkit reports combine these into a single query. The best way to migrate from the Cost Management connector is to copy the queries from a FinOps toolkit report and then update your visuals to use the CostDetails table.

  1. Download one of the FinOps toolkit reports.
  2. Open the report in Power BI Desktop.
  3. Select Transform data in the toolbar.
  4. In the Queries list on the left, right-click CostDetails and select Copy.
  5. Before you change your report, make a copy first to ensure you can rollback if needed.
  6. Open your report in Power BI Desktop.
  7. Select Transform data in the toolbar.
  8. Right-click the empty space in the bottom of the Queries pane and select New group….
  9. Set the name to FinOps toolkit and select OK.
  10. Right-click the FinOps toolkit folder and select Paste.
  11. Right-click the CostDetails query and select Advanced Editor.
  12. Copy all text and close the editor dialog.
  13. Right-click the Usage details query and select Advanced Editor.
  14. Replace all text with the copied text from CostDetails and select the Done button.
  15. Rename the Usage details query to CostDetails and drag it into the FinOps toolkit folder.
  16. Delete the Usage details amortized query.
  17. Select Close & Apply in the toolbar for both reports.
  18. Review each page to ensure the visuals are still working as expected. Update any references to old columns or measures to the new names.
    • Start at the report level:
      • In the Data pane, expand each custom table and check custom columns and measures.
      • In the Filters pane, check Filters on all pages.
    • Then check each page:
      • In the Filters pane, check Filters on this page.
    • Then check each visual on each page:
      • In the Filters pane, check Filters on this visual.
      • In the Visualizations pane, check Fields.

        If the column name was customized and you aren’t sure what the original name was, right-click the field and select Rename for this visual, then delete the name, and press Enter to reset the name back to the original column name.

If interested in custom columns and measures, see Copy queries from a toolkit report for required steps.


🧰 Related tools

  • 🏦 FinOps hubs – Open, extensible, and scalable cost reporting.


This site uses Just the Docs, a documentation theme for Jekyll.