Explore prior lessons on Measures here.
Measures are very powerful custom functions used inside Pivot Tables. However, when I started using them, I found myself getting easily twisted up between when to use SUM vs SUMX.
I built a cheat sheet to help me remember. Hope it helps you too.
The Cheat Sheet
Table1 below has Qty & Price. To calculate Sales, you could add a column to multiple Qty * Price as shown. However, calculating a value for each row uses a lot of memory, especially when you have lots of data.
Measures are a much more efficient way to calculate values for large data sets. There are several ways to write Measures to calculate sales, but only one way which avoids adding the Sales column to the Table above and results in the correct Grand Total of 20.
Note first, in all cases the sales for Item A have been calculated correctly, as well as Item B. The difference is the Grand Total. Adding 11 to 9 is 20, so with this small dataset this is an easy approach to check if a Measure is returning the correct result.
Sum of Sales
Sum of Sales was calculated the traditional way, by pulling the Sales column in to the Values section of the Pivot Table. It achieves the correct result. However, it can't be inserted inside another function such as Item Sales as a % of Total Sales. It also requires having a Sales column in the Table which isn't efficient.
Sales SUM
The next approach does not need a Sales column in the Table. This is a Measure which multiplies Price times Qty. This yields the correct result for the Sales of Items A and B, but the Grand total takes the combined price of Items A & B (11+3=14) times the combined Qty (1+3=4), which equals 14*4=56. This is incorrect as the sum of the sales for A (11) & B (9) is 20.
Sales Sum of Sum
When writing a Measure for the first time, I like starting with something I know will work and then building out from there. The Sales Sum of Sum utilizes the Sales column that was added to Table1. Taking the Sum of the Sales column yields the correct Grand Total. However, having an extra column isn't ideal.
Sales SUMX
SUMX is an iterator: it goes through each row and sums it. First the Sum of the Sales column was calculated, which was 20, then SUMX went through each row of the query and summed the sales. Effectively, this doubled each Item's sales. Using the Sales column in Table1, SUMX summed the total sales in row A & B, essentially doubling total sales. Wrong answer but an iterator has potential.
SUMX Sales
Now that's the right answer! But, it's using the Sales column again. However, it does tell us that SUMX could be used to get to the correct subtotal.
SUMX PQ
Finally, the sweet spot. SUMX PQ has the correct Grandtotal and does not require a Sales column added to the Table. This is the correct way to write a SUMX Measure.
Wrap-up
This cheat sheet shows all the wrong ways to write a Measure. When I tried to write DAX on the fly in the beginning, I've made every one of these errors. Happy DAX-ing!
In this lesson, you learned how to:
- Write SUMX Measures the wrong way, and
- Write SUMX Measures the right way.
Prior post: M-language
Next post: Relationship problems
Robert, Glad to hear another CPA is finding value in this content. Thanks for the feedback.
ReplyDelete