Do you still remember the excitement when you first wrote a DAX function that produce the correct calculation? Do you also remember the frustration when gradually you found DAX sometimes had mysterious behavior that threw you incorrect numbers? Today, let’s unbox one of the most common mysteries of DAX – AVERAGEX.
Today’s demo will be using AdventureWorks dataset.
The requirement is to calculate the average orders per product to see how popular the products are.
You came up with a solution:
- Calculate Total Order Quantity
- Calculate Avg. Orders per Product
So, you get these numbers which make you happy.
You want to check if this calculation is correct. So you create another measure Product Count.
Then, you drop the three measures in a table.
Wait, it looks not correct.
Do a math: Avg. Orders per Product * Product Count = Total Order Quantity? No, they don’t.
Why? To investigate it further, export the table into Excel where you can use simple functions to check.
Sum up column E “Check” and column C “Total Order Quantity” respectively, with the hope of the two sums match.
However, they don’t, again.
Why DAX gives me an incorrect average?
Let me tell you. DAX is correct even it looks mysterious. Ask yourself, do you really want the average is based on all the products regardless of if they were sold or not?
You don’t want that absolutely. The average should be based on the sold products, not all products.
There is no issue with DAX and the numbers. You just need to adjust the way you check, or say, the way you understand the average. Some of the products even don’t have any orders so they have been excluded from the average. That’s why you want to exclude them in your check as well.
Let’s create another measure Sold Unique Product Count just to visualize what happen with the average. The count of 334 excluded those products that had no orders.
Drop it in the previous table.
Now do the same thing in Excel. This time, you need two total order checks:
Then sum the orders up.
Now you see the Sold Unique Product Count achieves the match.
The reason is simple, those products are related to the total order quantity.
However, the “Total’ misled you, and users.
How about change a name?
Now there is no misleading, so there is no misunderstanding.
