A **DAX **code will be used to find average sales, commission, success rate of broker calls, and the greatest number of calls.

**Download Practice Workbook**

### Example 1 – Finding the Average Sales Using Power Pivot Measures

To create a pivot table:

- Select the whole dataset.
- Choose
**Insert****>> Pivot Table >> From Table/ Range**.

- Select a
**New Worksheet**to create the**PivotTable**. - Check
**Add this data to the Data Model**and click**OK**.

- Check the fields shown below.

- Right-click
**Range**and select**Add Measure**.

- Choose
**Average Gross Sales**in**Measure Name**. - Enter the following formula in the
**Formula**box. - Click
**OK**.

**
**

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

- Check
**Average Gross Sales**to see the output.

### Example 2 – Finding the Commission Using Power Pivot Measure

- In the previous
**Pivot Table:** - Right-click
**Range**in**PivotTable Fields**to create another**Measure**.

- Name the measure and enter the following
**DAX**formula 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`

` )`

` )`

- Click
**OK**.

**Formula Breakdown**

The code snippet uses 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; Below **20**, a **3%** rate is applied. Otherwise, no commission is given, returning a blank output.

- Check
**Commission**. - This is the output. Use the
**Percentage**.

### Example 3 – Finding the Total Order

The dataset was modified and named **Table3**.

- Insert a pivot table.

- Add a new
**Measure**for**Table3**. - Enter
**Total Order**in**Measure Name**and use 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 Breakdown**

The **DAX **code uses the Power Pivot’s **SUMMARIZE** function to group data in **Table3 **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. 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 computes the total value for each individual by summing their monthly data and provides an overall sum for all individuals combined.

This is the output.

### Example 4 – Finding the Success Rate

- Insert a pivot table.
- Add measures.
- Add
**Success Rate**in**Measure Name**and enter 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 Breakdown**

The** 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**. It groups data based on names, computing the sum of specified months for each group, and 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 identifying the highest success rate.

This is the output.

### Example 5 – Ranking According to the Success Rate

- Insert a pivot table.
- Add measures.
- Add
**Rank**in**Measure Name**and use the**DAX**formula.

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

**Formula Breakdown**

The **DAX **formula calculates the rank of each individual in **Table3 **based on their **Success Rate** values. It considers all rows in **Table3**, orders them in descending order, and assigns a unique rank to each individual based on their relative performance.

This is the output.

## Things to Remember

- Measures can be reused across multiple reports.
- Keep measures concise and relevant.

## 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?
**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 a better performance.

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