Analyzing Azure EA Cost Using Power BI – Part 3 Advanced Calculations

 

Analyzing the usage and tuning resources is a key responsibility in Cloud Management. We need to understand where we spent , what are the trends and where we can tune our spending. When it comes to analyzing Azure Cost Microsoft offers different tool set with different capabilities;

  • Cloudyn
  • New Azure Cost Management
  • Azure Consumption API Connector for Power BI
  • In this blog post I would get into details of using Consumption API to bring cost metrics into Power BI and we will be using Power BI capabilities for analyzing the cost. Power BI gives you the most flexibility when it comes to reporting and customization. In this blog post series I will get into MoM /YoY analyses, distributing the cost between departments / sub companies , custom charge back options etc.

    For initial setup and Power BI template check Part 1

    For extracting tags and bring them into Usage Analysis check Part 2

    In part 3 I’ll focus on Month over Month / Week over Week usage analysis , rolling usage and distributing usage among departments/customers/companies or any other category we want.

     

    Disclaimer :I’m not a Power BI expert. This work is mainly collection of exercises we have gone through with our customers. There are multiple ways of achieving the results in Power BI and these are the ones we have seem to make this work……

    Using Time Intelligence MoM Usage Analysis

    To calculate the Month over Month , Week Over Week, we can utilize measures in Power BI .

    Here is how to calculate the cost for the previous month using calculate function ;

    PrevMonthCost = CALCULATE(SUM(Usage[Cost]),PREVIOUSMONTH(Usage[Month]))

    Now we can use this measure together with Usage[Month] field to visualize increase/decrease in usage Month over Month.

    MoMGraph

    MoM

    Note that Month over Month calculation gives us the right values only for completed months. You can use month slicer  on top to remove last month from the calculation.

    Calculating Cumulative Usage

    To calculate rolling sum for usage, we again utilize measures. Lets create a new measure as follows;

    Cumulative Usage = CALCULATE( sumx(Usage,Usage[Cost]), FILTER( ALLSELECTED(DateKey),DateKey[Date]<=Max(DateKey[Date])))

    This will sum up the cost for the dates before the current value .

    Now we can use this new measure instead of Cost field to visualize UsagetoDate.

     

    TagCumulative

    Distributing Cost based on Static Mapping

    In some cases when we manage the EA usage  , we need to show usage distribution for specific departments , customers or companies we host resources for. If those customers are sharing the resources  line a single VM or storage account  or a SQL instance in Azure , there is no built in way to distribute usage among those customers. Here first, I’ll show a way to distribute the resource usage using a static mapping table. For example, lets assume we share our platform to host workloads for 5 companies. By using a static table we will show how usage is distributed among these 5 companies.

    To fill the table

    • Step 1 – Click Enter Data
    • Step 2 – Rename Column1 to Platform and add the platforms we need to distribute the usage
    • Step 3 – Add new columns and name them as your customers/Departments/Companies etc and will the values as percentage of the cost which should be reflected by that customers/Department/Company
    • Step 4 – Rename Table to CostMapping

    Here out mapping will look like

    costmapping

    Now we can create new measures to calculate usage for specific customer/department/company

    UsageCompany1 = [Cost] * AVERAGE(‘CostMapping'[Company1])/100

    Repeat same for all 5 companies to show how usage is divided among these 5 companies .

    Now we can utilize our platform tags and company usage distribution to find the cost allocation ;

    CostbyCompany

    Distributing Cost based on Dynamic Values

    Just like static mapping we can utilize App Usage / SQL Record Counts / Transactions to estimate % of the cost distribution  for respective companies / departments. For example you can use asset intelligence to count page view for the respective month and map it with company information. Power BI has many connectors to go fetch the data from Azure SQL , Log Analytics  or Application Insights ,  we can bring all this data together  and correlate all of them.

    Download link for the template

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.