# Excel Power Pivot Measures (5 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Power Pivot measures are a powerful way to perform calculations and analysis on your data in Excel. They allow you to create custom calculations, such as sums, averages, and ratios, that dynamically update as you filter and manipulate your data. Measures provide insights and enable you to make informed decisions based on your data, all within the familiar Excel environment. We have numerous Power Pivot measures examples to clarify this topic.

In this article, we will use DAX code to find out average sales, commission, success rate of broker calls, which broker is more successful, and who has given more calls.

## Use of Power Pivot Measures: 5 Examples

In this section, we are going to explore five different examples to show the use of DAX code in Power Pivot Measures in Excel. Dax code has the capability to manipulate every pivot data efficiently so that you can literally take over the pivot table.

### 1. Finding Average Sales Using Power Pivot Measures

Before moving to Power Pivot, we need a Pivot Table. To create a pivot table, we have to follow the following steps:

• We will select the whole dataset.
• Choose Insert >> Pivot Table >> From Table/ Range.

• Select New Worksheet to create the PivotTable in new worksheet.
• Then check to Add this to the Data Model and press OK.

• Now choose required fields to set up the PivotTable as shown in image below.

• Press right-click on Range and select Add Measure.

• The Measure window appears.
• Set Average Gross Sales to Measure Name.
• Insert the following formula into the Formula box and press OK.

`=[Sum of Gross Sales]/[Sum of Total Quantity Sold]`

• Finally, check the Average Gross Sales option from the right side to get the desired output.

### 2. Finding Commission Using Power Pivot Measure

Here, we will find out the commission based on condition using Power Pivot Measure.

• We will continue the previous Pivot Table.
• Right-click on the Range option from the PivotTable Fields to create another Measure.

• Set a name of the measure and insert the following DAX formula for the commission in the Formula box.

`=IF (`
`Â  Â  Â  Â  [Average Gross Sales]Â  > 20,Â Â Â Â  // Above \$20`
`Â  Â  Â  Â  0.05,Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  // 5% Commission`
`Â  Â  Â  Â  IF (`
`Â  Â  Â  Â  Â  Â  Â  [Average Gross Sales]Â  < 20, // Below \$20`
`Â  Â  Â  Â  Â  Â  Â  0.03,Â Â  Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â // 3% Commission`
`Â  Â  Â  Â  Â  Â  Â  BLANK()Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â  // No Commission`
`Â  Â  Â  Â  Â  Â  Â  )`
`Â  Â  Â  Â  )`

• Finally, press OK.

ðŸ”Ž Formula Explanation

The provided code snippet employs nested conditional statements to determine commission rates based on the value of Average Gross Sales. If the sales are above 20, a 5% commission rate is assigned; if below 20, a 3% rate is applied; and if neither, no commission is given, resulting in a blank output.

• Check the Commission option from the right window.
• Finally, we get the commission column. Do not forget to use the Percentage.

### 3. Finding the Total Order

We will use a modified dataset to get the total in Power Pivot Measure. We have formatted our dataset into table named Table3.

• Like before insert a pivot and necessary data.

• Then, add a new Measure for Table3.
• Now, input Total Order column in Measure Name and write the DAX formula.

`=SUMX(`
`SUMMARIZE(`
`'Table3',`
`'Table3'[Name],`
`"Total",`
`SUM('Table3'[Jan]) + SUM('Table3'[Feb]) + SUM('Table3'[Mar])+SUM('Table3'[Apr])+SUM('Table3'[May])+SUM('Table3'[Jun])`
`// Add more columns as needed for the sum`
`),`
`[Total]`
`)`

ðŸ”Ž Formula Explanation

The DAX code uses Power Pivotâ€™s SUMMARIZE function to group data from the Table3 table by unique values in the Name column. Within each group, it calculates the sum of values for different months (Jan to Jun). The code creates a new calculated column called Total to store these sums. Then, the outer SUMX function iterates through each group created by SUMMARIZE and calculates the sum of the Total column, resulting in the grand total of the calculated monthly sums for all names. This code essentially computes the total value for each individual by summing their monthly data and provides an overall sum for all individuals combined.

• Finally, we get the total order.

### 4. Finding Success Rate

• Like before insert a pivot and necessary data.
• Now, add Success Rate column in Measure Name and write the DAX formula.

`=[Sum of Stock Sold]/SUMX(`
`SUMMARIZE(`
`'Table3',`
`'Table3'[Name],`
`"Total",`
`SUM('Table3'[Jan]) + SUM('Table3'[Feb]) + SUM('Table3'[Mar])+SUM('Table3'[Apr])+SUM('Table3'[May])+SUM('Table3'[Jun])`
`// Add more columns as needed for the sum`
`),`
`[Total]`
`)`

ðŸ”Ž Formula Explanation

The given DAX formula calculates the ratio of Sum of Stock Sold to the total sum of monthly values (Jan to Jun) for each individual in the Table3 table. It achieves this by grouping data based on names, computing the sum of specified months for each group, and then dividing the Sum of Stock Sold by the calculated total. The result offers insights into how the stock sold compares to an individualâ€™s overall monthly activity, aiding in performance evaluation and analysis and, furthermore, help us to find out who has the highest success rate.

• Finally, we get the column of Success Rate.

### 5. Ranking According to Success Rate

• Like before insert a pivot and necessary data.
• Now, add Rank column in Measure Name and write the DAX formula.

`=RANKX(ALL('Table3'), 'Table3'[Success Rate],, DESC)`

ðŸ”ŽFormula Explanation

The DAX formula calculates the rank of each individual in Table3 based on their Success Rate values, with a higher rank indicating better success. It considers all rows in Table3 for ranking, orders them in descending order of success rates, and assigns a unique rank to each individual based on their relative performance. This measure can assist in identifying and comparing individuals based on their success rates, aiding in performance assessment and decision-making.

• Finally, we get the Rank.

## Things to Remember

• Measures can be reused across multiple reports and easily shared with colleagues.
• Overcomplicating your data model with too many measures can impact performance and make the model harder to manage. Keep measures concise and relevant.
• Avoid creating circular references where measures reference each other and cause incorrect results.
• Handle potential division by zero errors in your calculations to prevent display issues or incorrect results.

1. How do I troubleshoot when a measure displays unexpected results?
Double-check your DAX formula, filters, slicers, validate your source data and measure logic for accuracy.

2. Are Power Pivot measures compatible with Power BI?
While Power Pivot and Power BI use similar DAX language, measures may need adjustments when transitioning between the two platforms.

3. How do I optimize performance when working with Power Pivot measures?
Limit complex calculations, avoid circular references, and optimize your data model for better performance.

Utilize online tutorials, courses, and forums to deepen your knowledge of DAX and Power Pivot for advanced calculations.

## Conclusion

In summary, Power Pivot measures in Excel help users work smarter with their data. By using special formulas called DAX, measures can do math and analysis for you. They adjust when you change filters, which makes them super handy. You can find trends, do calculations, and make better decisions using measures. With practice, youâ€™ll become a data pro, finding insights and making your work easier.

<< Go Back to Power Pivot Formulas | Power Pivot ExcelÂ |Â Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Joyanta Mitra

Joyanta Mitra, a BSc graduate in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. Specializing in programming, he has authored and modified 60 articles, predominantly focusing on Power Query and VBA (Visual Basic for Applications). His expertise in VBA programming is evident through the substantial body of work he has contributed, showcasing a deep understanding of Excel automation, and enhancing the ExcelDemy project's resources with valuable... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF