One of the challenges of understanding your Azure usage is to decipher the usage report from the Azure Portal. And I really needed that, since I was getting past my monthly cap consistently. Since I’m not an Enterprise user that can use this FREE amazing tool, I decided to figure out what was going on with my MSDN subscription. My first step was to download the usage report from the Azure account portal:
then:
Pick Version 2 – Preview.
Once done, the CSV file you download has two parts. The first shows the summary of utilization per Meter type.
Actually, it is based on these 3 items:
These 3 together are the key to find the utilization per resource. Column O has the Rate we need in order to find the final cost of single resource. But why is that necessary? Look at the example below, which comes from the second part of the CSV file:
Note that you don’t have a cost per line, only the Consumed quantity. So, how can we know? The answer is in columns D, E and F in the second table, which are exactly the same ones used in the first table:
Now, if I could grab the Rate from the first table and assign it to each line on the second one based on these 3 columns, wouldn’t it be great?
Enters a slight Excel tweak and Power BI. The first thing is to extract the first peace of the CSV file and turn into a separate tab. Let’s call it RateTable:
Now, the remaining rows need to be alone in another tab. Let’s call it azureusage:
Now let’s save the CSV as an Excel file and leave it ready.
If you don’t have Power BI Desktop, go here to get it. Once there, you can just add data to it:
Select Excel and point to your file:
You should see both tabs:
Now click on Edit and PBI will take you to the query editor view. There, we will need to execute a few steps to get the proper information out of our data.
1. The first thing is to remove blank rows from the RateTable query:
Also make sure you remove unused rows, like the Daily usage title that comes originally from the initial CSV file.
2. Next, we need to create a custom column on both queries, to create a unique key (just so we can relate both of them). We will start with the query we have opened. Select Meter Name, meter sub-category and meter zone columns, in this order, and select Merge Columns:
Give it a name:
You should now see a new column there:
3. Repeat the process for the azureusage query:
4. Now let’s create a relationship between the two queries. Click on Close and Apply to save your changes:
Once there, click on the relationship icon: 
Once there, you can try to detect the relationship. Click on Manage Relationship up-top and then Autodetect:
Isn’t it cool? 
You could have added it manually or even just connected the fields between the two tables:
6. Great. Back to Edit Queries. In this step will add corresponding rate for each usage line, based on the type of meter (and meterkey) we have just created. To do that, go as follows:
– Click on Merge Queries up-top while in the azureusage query. This dialog will show:
Select as below (MeterKey on both of them):
This creates a new column. We don’t need all the tables returned. To select what we need (Rate), click on the arrows icon:
And select the Rate only:
Rename the column to Rate.
7. Now, all you need is something that calculates the cost for that entry, by multiplying the Rate by the Consumed Quantity. To do that, you click on Add Column:
And Add a Custom column:
Click Ok. Now set the type of the data in the column:
Now close and Apply.
8. Back in the main canvas, select on type of visualization and the Cost and Instance ID on the right side:
And there you have it: your cost per individual resource in Azure:
It is kind of a long tutorial, but might be a good way to visualize you detailed cost per instance.
Hope this helps!