SUMX total is not adding up correctly

[Anonymous]

I'm having issue where I'm trying to get totals based on two columns and it's not multiplying correctly, I tried following the solution in: https://community.powerbi.com/t5/Desktop/Help-with-SUMX-Calculating-Total-Sales/m-p/501410#M234002  which the OP has same similar problem as mine. However, one of my columns is based on a COUNT formula using a measure and is not available as a column to use with the SUM formula. I can't create the COUNT formula as a column since it then tries add up everything in the table. Or maybe I'm going about it the wrong way. The first column is commission rate (Storefront_Commission) and second column is Qty (CountSalesCode) and last column is total (Total Commission Amt Due).

My measure for last column is:

Total Commission Amt Due = SUMX(All_AMS_Sales, All_AMS_Sales[CountSalesCode] * RELATED(Product_Billing[STOREFRONT_COMMISSION]) 

I've highlighted all the ones in yellow that are totaling up incorrectly. 

I've confirmed the qty columns are set to whole numbers and 1st and 3rd column are set to US Currency with 2 decimal places. I'm using the COUNT because I need to add up product in the original sales order record and cannot rely using the qty in the sales order record because if someone ordered 2 items, we only pay commission once per order.

Thanks!

[Icey]

Hi @Anonymous ,

Please share me some dummy sample data, not real data, for test.

Best Regards,

Icey

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490