DISTINCTCOUNT of Table A column multiply by column of Table B

[smko]

Greetings developers, do point me to the right direction if this has been posted somewhere because I found none. I have a simple problem but does not seems to have simple solution. Basically I need to calculate the order fee per unique order, transactional data is in Sales Table, and fee information is in Charges table.

I have tried various method including blocking context transition by using variable in the SUMX on Charges table, either the row level is correct but total is incorrect or vice versa. These two are separate table from the source. Appreciate if anyone could lend a helping hand for a frustated analyst. I have attached a link to the pbix file.

Expected result:

Platform	Orders	Order Fee
DEF	2	7
XYZ	1	5
Total	3	12

https://clickasiaca-my.sharepoint.com/:u:/g/personal/sm_ko_clickasia_asia/Ef09ekohQJpPpIZnX0qsjG8BNJ... 

[amitchandak]

@smko , You can create a new columns in Sales

Column = Maxx(filter(Charges, Charges[Date] = eomonth(Sales[Date],0) && Charges[Platform] = Sales[Platform]), Charges[Charges per order])

[smko]

Hi @amitchandak , appreciate if you could provide guidance on this matter

[smko]

Hi @amitchandak, thanks for helping. However the sales table has lineitem, the logic you wrote will return the charges to each of the lineitem, multiplying OrderID will equal to inflated value. Is there any alternative solution for this?