Forum Discussion
SUMX - problem subtotal
Hi
I my fact sales table I have a Pcs_grouping column depending on how many pcs the customer bought of that item. See below.
I try to calculate the best price for the customer with Best_Gross
Best_Gross:= sumx(values(vDIM_Items[ItemID]),[QTY_FromFactSalesTable]*[BestPrice_FromItemtable])
This is working fine if I put "Pcs_Grouping from fact sales table" in my rows.
But if I put "ID_pcs from dimension table" in my rows the subtotals are incorrect. This table is a related dimension table. See below
Can anybody help correcting my SUMX formula so I can use "ID_pcs from dimension table" with correct subtotals?
The
3 Replies
Please see this video for how to fix this.
(2) Power BI - Tales from the front #01 - Getting the Right Total - YouTube
Pat
Thanks - great video.
However I could not fix my problem. The QTY are grouped corrrect but I there is a problem when multiplying with best itemprice for each item, coming from the related item table.
Would appreciate some helt with the formula
Hi, Dennis_Pedersen
1) Uploading files
Especially when you have a problem with a DAX statement or the data model, it would be best to provide a pbix-file containing the sample data. You might have seen posts here in the forum where files are directly attached and have searched for the button to do exatly that. But unless you're a Microsoft employee or a super user this feature will not be available for you. Instead you have to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post.
