If you have not yet introduced yourself to the basic concepts of flex.bi then please start with an overview of flex.bi concepts and learn how to analyse and create reports. This tutorial will explain how to define new calculated members using MDX calculation formulas.
If you have never worked with MDX before, we suggest you watch this video: MDX basic concepts
Dimensions and hierarchies
...
Now that you know how to reference other existing measures and other dimension members we can create simple arithmetic calculations.
If we have the [Measures].[Store Sales] and [Measures].[Store Cost] measures then we can define a new calculated measure - Profit (which will have the full name [Measures].[Profit]) with the formula:
| Code Block |
|---|
[Measures].[Store Sales] - [Measures].[Store Cost] |
When you use the new Profit measure in your flex.bi reports then this formula will calculate the difference between the Store Sales measures value and the Store Cost measures value.
You can also use calculated measures in other calculated measures (it is important to define them in the right sequence - you can only use existing calculated members). For example, you can now define [Measures].[Margin %] with the formula
| Code Block |
|---|
[Measures].[Profit] / [Measures].[Store Sales] |
...
and it will calculate the margin as a number from 0 to 1. If you would like to display the results as a percentage then change the Formatting of this calculated measure to use integer or decimal percentage formatting.
You can also perform arithmetic calculations for calculated members in other dimension. For example, you could define [Customers].[West coast] calculated member in the Customers dimension with the formula
| Code Block |
|---|
[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA] |
and now when you combine the calculated members in reports [Customers].[West coast] with [Measures].[Store Sales] you will get the total sales for all these three states together. If you combine it with [Measures].[Profit] you will get the total profit for all these three states.
...