Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smko
Helper I
Helper I

DISTINCTCOUNT of Table A column multiply by column of Table B

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:

PlatformOrdersOrder Fee
DEF27
XYZ15
Total312



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

3 REPLIES 3
amitchandak
Super User
Super User

@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])

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

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.