Blog

If you are having issues with the total row of tables in DAX, this may be a familiar sight:

Image

 When actually, all you want is this:

Image

So, what’s going on here? Why does my first table multiply the totals together, but my second table sum the values in the column above? Stuart Box, Owner of Burningsuit explains.

Our solution to create the correct table

In the first measure, “Sales V1”, I used this DAX expression:

Sales V1 = SUM (Sales [Price]) * SUM (Sales [Qty])

The first thing to note is that I only have one price and quantity per product so, for each product listed in the table, the SUM function is simply summing a single value. That is, until the Total row is calculated when SUM does now sum up all the prices and quantities. My DAX expression just multiplies these values together, as might be expected i.e. 30 x 60 = 1800.

Please note that this version (which I also see a lot of), using SUMX would give you the same result for the same reasons:

Sales V3 = SUMX (Sales [Price]) * SUMX (Sales [Qty])

In the second measure, “Sales V2”, is used in this DAX expression:

Sales V2 = SUMX (Sales, Sales [price] * Sales [Qty])

It is this expression that uses SUMX that has the quality of being able to visit every row in the source table (3 rows in our case) and FOR EACH ROW in the source table, performs the multiplication. Of course, it does this in memory but it’s just like you had created your own calculated column in the Sales table that multiplied the price by quantity (which would, of course, also have solved this problem).

Image

It is this calculation that, when put into the table visual is then summed for each product sitting in the rows of the visual. As there is still only one price and quantity for each product, the SUMX function is summing a single value (50, 200 etc). However, when it comes to calculating the Total row, it sums up the result of this calculation for ALL the products (i.e. 50+200+450=700) and so returns the correct result. So in short, you could say that the first measure sums then multiplies, the second measure multiplies and then sums.

Join our DAX training courses

DAX tables are extremely useful and important when it comes to understanding your data and getting to grips with the DAX language can seem frustrating and challenging.

Understanding the key concepts of the DAX language can empower you to find solutions to any future hiccups you may have when analysing data. If you’re looking to improve your DAX skills, then attend our one day DAX for Power BI course, where we cover all of the basics of the language and functions.