# Average amount transactions

Hi Sam/all,

I’m pretty confuse on how to use DAX Average function, although I did look at your Youtube about Average per transaction. Been reading and try to digest the reading material from SQLBI about context transition, but I still not getting what I want.

The thing is, I have a transaction data, which has some duplicate value which causing the amount to be doubled. The data is something like this :

SalesID SalesTeritory OrderDate Price Qty Location
SO-01 EAST 1/1/2019 500 1 Main W/H
SO-02 EAST 1/1/2019 100 1 Main W/H
SO-03 WEST 1/9/2019 200 2 Main W/H
SO-03 WEST 1/9/2019 200 2 Aisle 2
SO-04 WEST 1/3/2019 300 4 Main W/H
SO-05 WEST 2/1/2019 100 7 Main W/H

In above table, SO-03 is doubled . This is because the location is actually from other table and after join table, that is the result.

So I think, I just create a measure and average the amount, that would solve the problem. It turned out it’s not that easy.

At first I have a simple measure for the total :
[Total Amount] = SUMX(
Sales,
Sales[Price] * Sales[Qty])

After I have duplicate rows, I think I can create this measure :
[AVG Total Amount] = AVERAGEX(
VALUES(Sales[SalesId]),
[Total Amount]
)

However when I put in Table visual, just display the Sales Id and that Measure, I saw the amount is doubled.

There is some resources suggesting to put CALCULATE in that [Total Amount], but the result is the same.

1 Like

Hi,

You can use the summarize function to create a summary table with unique values. Then calculate average based on summary table.

summary = SUMMARIZE(Sales,Sales[OrderDate],Sales[Qty],Sales[SalesID],Sales[SalesTeritory],Sales[Price])

average = AVERAGEX(summary,summary[Price])

Thanks
baiju

Hi,
are you suggesting to create virtual table in my measure then Return AVERAGE of it ?

Because I cannot use SUMMARIZE since I’m using Direct Query.

Thanks,

Hi,

Yes. with create table option in modelling table. I think summarize works with direct query too.

No it’s not.
It will force to Mixed mode which I’m not allowed to do so.
Thanks

ok. in that case you will need to create a calculated column using rankx function to generate rownumber for each sales ID. then you can you can filter out the duplicate entry before doing average calculation.