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.
Download Practice Workbook
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.
- Then, add measures.
- 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.
- Then, add measures.
- 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.
Frequently Asked Question
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.
4. How can I learn more about creating advanced Power Pivot measures?
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
Get FREE Advanced Excel Exercises with Solutions!
