Forum Discussion
Issues divinding SUM by COUNT columns
Hoping someone can help because I feel like I'm missing something very simple and it's driving me crazy. I am just trying to calculate the Average Transaction Value (ATV) for each row (see below). The semantic model lists, by transaction number, every transaction where a coupon code was used, the coupon code and what the discount on the transaction was.
The total discounted works fine, and for transaction number I just dragged the transaction ID field to values and changed SUM to count and that's ok too. The total turnover is a bit trickier since due to having issues with that original data source so I've had to DAX calculate it (if Coupon Code = "25% off" then ((Total Discount/25)*100)-Total discount))). Checked with the retail software directly and this works too.
However I'm hitting a wall with ATV. I just assumed it would be SUM(Total Turnover)/COUNT(Transaction Number) and for the first row I know the answer should be €78.03, but it just returns 0. I tried switching COUNT with ABS and SUM and that gives me completely wrong answers too. I'm reasonably confident the turnover bit is fine in the calculation, I'm pretty sure the transaction ID bit is the culprit.
I feel like I'm being stupid. Can anyone help?
Hi, BUC
Based on the picture you provided, I extracted part of the data, as shown in the image below:
I've created a table visual as shown in the image below:
You can follow the steps below to set up a correct ATV measure:
Right-click in your visual and click New Calculation in the menu that pops up:
Enter the following DAX formula in the window below and click √ next to it:
ATV = DIVIDE ( [Sum of Total Turnover], [Sum of TransactionsNumber] )Notice that you're entering two columns from your table visual:
Here are the results:
Hope the above can be helpful to you.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi, BUC
Based on the picture you provided, I extracted part of the data, as shown in the image below:
I've created a table visual as shown in the image below:
You can follow the steps below to set up a correct ATV measure:
Right-click in your visual and click New Calculation in the menu that pops up:
Enter the following DAX formula in the window below and click √ next to it:
ATV = DIVIDE ( [Sum of Total Turnover], [Sum of TransactionsNumber] )Notice that you're entering two columns from your table visual:
Here are the results:
Hope the above can be helpful to you.
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Jianpeng Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.