topic SumX not working properly - filter problem? in DAX Commands and Tips

SumX not working properly - filter problem?

Dennis_Pedersen — Wed, 20 Mar 2024 09:50:41 GMT

I want to calculate my total netweight sold as a measure (not calculated column)

So I just want to multiply the qty sold with the netweight from my itemtable.

As you can see from the 2 matrix below the 2 measures "Netweight from Item" and "SUMX(ALLSelected-wrong" works fine in the first matrix where only items are included. However when customers are added in the 2 matrix you see the problem "Netweight from Item" is blank for 1 customer.

As you can see from the measure "Netweight from Item" I also tried removing filters from vDIM_customer table. But that cannot work as I then get a memory error.

So I hope you have another solution ?

Netweight from Item:

CALCULATE(
average(vDIM_Items[NetWeight])
, REMOVEFILTERS(DIM_Time)
// , REMOVEFILTERS(vDIM_Customer)
)

SUMX(ALLSelected-wrong:

sumx(allselected(vDIM_Items[ItemID]),CALCULATE([QTY]*[Netweight from Item]))

Re: SumX not working properly - filter problem?

Anonymous — Thu, 21 Mar 2024 08:28:29 GMT

Hi Dennis_Pedersen

You can try to modify the formula as follows:

Netweight from Item:

CALCULATE( average(vDIM_Items[NetWeight]) , ALLSELECTED("Your table name", Yourcolumnname) )

SUMX(ALLSelected-wrong:

sumx(allselected(vDIM_Items[ItemID]),[QTY]*[Netweight from Item])

If the problem persists, please provide detailed sample data: How to provide sample data in the Power BI Forum - Microsoft Fabric Community . Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

Best Regards,
Yulia Xu

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

Re: SumX not working properly - filter problem?

Dennis_Pedersen — Thu, 21 Mar 2024 11:20:35 GMT

Thanks but unfortunatetely it looks the same.

You mean formulas like below?

Netweight from Item:

CALCULATE(
average(vDIM_Items[NetWeight])
, ALLSELECTED(vDIM_Items[ItemID])

SUMX(ALLSelected-wrong:

sumx(allselected(vDIM_Items[ItemID]),[QTY]*[Netweight from Item])