If you are having issues with the total row of tables in DAX, this may be a familiar sight:
When actually, all you want is this:
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).
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 can seem a frustrating and challenging language to get to grips with but understanding some key concepts can allay these frustrations empower you to find your own solutions. Why not attend our DAX training course where we can help you on this journey?