Average amount transactions

DAX DAX Calculations
1

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.

Can you help me to understand more about this issue ?

Many thanks in advance.

1 Like
2

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

3

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,

4

Hi,

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

5

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

6

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.