DAX : The SUMX function

Introduction

When do you  need the SUMX() function? Easy question but more difficult to answer. One of my current projects is about analyzing the sales data with the PQM analysis. What is PQM analysis, I hear you say? Well, PQM stands for Price, Quantity Mix analysis or sometimes it's called PVM analysis: V for Volume. And one of the calculations that needs to be done is calculating the Sales amount based on Quantity Sold and Price per Unit. S

In this blogpost I'll show you an implementation of the SUMX function.

Example data

Below, I've created an example in Excel and I've read it into PowerPivot for calculating the Sales amount. Here we have some categories, Products, Periods with Quantity and Price.

The first step in the PQM analysis is calculating the SalesAmount. 

SUMX Function

The next step is loading the data in PowerPivot and calculating the Sales Amount. So I added the data to the model in PowerPivot and calulate the SalesAmount with the following DAX Formula:

       
 Sales:=SUMX(Sales; Sales[Price] * Sales[Quantity])

SUMX is an iterator and this means that SUMX iterates over the table, in this case the Sales Table and it calulates a formula: Sales[Price] * Sales[Quantity]. So, it calculates for every row Price * Quantity. Below an example:

Sales Product A : 1 * 100 + 2 x 85 + 3 x 100 + 2 * 90 + 2 * 85... = 1640

Below, the result of this calculation in the Pivot table:

And the SUMX function calculates the Sales amount also on other attributes:

The beauty of PowerPivot, automate the data analysis and still very flexible.

Conclusions

This is the first step in the PQM analysis. In this blogpost I've used the SUMX function for calculating the Sales amount with expression Price x Quantity per row. The SUMX function is an iterator.

Greetz,

Hennie