Cube.Measures

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Cube.Measures

What is Cube.Measures?

Cube.Measures is a Power Query M function that allows users to create measures in a cube or tabular model. Measures are calculations that are used to summarize and analyze data in a pivot table or chart. They can be used to perform a variety of tasks, including calculating totals, averages, percentages, and ratios.

Measures are created using the DAX language, which is a formula language used in Power Pivot, Power BI, and Analysis Services. However, Cube.Measures allows users to create measures using M code instead of DAX. This makes it easier for users who are more familiar with M code to create measures and perform complex data analysis.

How to Use Cube.Measures

To use Cube.Measures, you’ll need to have a basic understanding of Power Query and M code. Once you have this, you can follow these steps:

1. Open Power Query and load your data into the query editor.

2. Click on the Home tab and select “New Source” > “Blank Query”.

3. In the formula bar, type “Cube.Measures” followed by a set of parentheses.

4. Inside the parentheses, enter the name of your measure, followed by a comma.

5. Next, enter the M code that defines your measure.

6. Press enter to create your measure.

The M Code Behind Cube.Measures

Cube.Measures uses M code to create measures that can be used in a pivot table or chart. The M code defines the calculation that the measure will perform. Here’s an example of M code that calculates the total sales for each region:

Cube.Measures(“Total Sales”, each List.Sum([Sales]))

Let's break down this code into its individual components:

- "Total Sales" is the name of the measure.

- "each List.Sum([Sales])" is the M code that defines the calculation. This code uses the List.Sum function to add up the values in the "Sales" column.

Cube.Measures also allows users to create more complex measures that use multiple columns and functions. Here's an example of M code that calculates the percentage of total sales for each region:

Cube.Measures(“Percent of Total Sales”, each List.Sum([Sales]) / List.Sum([Sales]) over All([Region]))

This code uses the List.Sum function to calculate the total sales for each region, and then divides this value by the total sales for all regions.

Cube.Measures is a powerful tool for data analysis and modeling in Power Query. It allows users to create measures using M code, making it easier for those who are more familiar with M code to perform complex data analysis. In this article, we've explored the M code behind Cube.Measures and demonstrated how it can be used to create measures that perform a variety of calculations. With this knowledge, you can take your data analysis to the next level and uncover new insights into your data.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)