Grand Total Not Correct after Using REMOVEFILTERS

[mpuck]

Hi, 

I am new to Power BI but I am constantly learning. I have tried so many different ways (posts, Youtube, etc) of solving this issue and am having no luck.  Any help would be greatly appreciated.

I am trying to create a drill down to item level data to include items purchased by a vendor selected in the summary, the purchase amounts of those items and the sales amounts of those items. I used removefilters in the sales formula to remove the "source no." from the drill through filter. The source no is the vendor no or the customer no depending on the document type. 

This works fine for the line totals but the grand total is the sum of all items for the time period. 

Please see my pbix file here: TestFile 

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[mpuck]

I tried using a SUMX formula and referencing my sales measure and it appeared to work but in the my real file if the vendor has multiple items and one of those items doesn't have a purchase for the time period, then the sales amount is not on that item row.  See below.  The Sales by Item column is correct, but the grand total is incorrect. The SUMX column grand total is correct, but the sales for the items without a purchase in that time period are omitted. 

[Anonymous]

Hi @mpuck 

You can try the following code.

Measure =
SUMX ( VALUES ( 'Table'[Item No_] ), [Sales] )

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mpuck]

Hi ,

I tried that and the grand total works but it is ignoring sales for items that didn't also have a purchase in the same time period.  Below is a screenshot from my original file. YOu can see that there are sales for that item that show in the original formula but they do not show with the SUM X formula .