0

I have a table 'Sales' where we capture all sales. We trade mainly on a 13 digit material number, where the last 3 digits define the country of manufacture. So we also maintain a 10 digit material number too, which defines the unique product.

In some cases we move the manufacturing plant from one place to another, and update the master data to reflect the new country as the main material and it becomes a stock item. The other countries are marked as non-stock, but the sales history remains on those country indexes.

My issue is that I want to see on any material, what is the total sales on that 10 digit material, and sum that up.

I'm using a calculate function, but I just can't get this to work. Can anyone help?

Formula:

TGS (LC) 10 Digit = CALCULATE([TGS (LC) Total], Filter(Sales, Sales[Material number 10-digit]))

Error Message:

MdxScript(Model) (23, 77) Calculation error in measure 'Sales'[TGS (LC) 10 Digit]: Cannot convert value '018999901M' of type Text to type True/False.

Query1

Table2

Stephen John Cross
  • 1

1 Answers1

0

Give it a try:

TGS (LC) 10 Digit = 
    CALCULATE(
        [TGS (LC) Total], 
        Filter(
            Sales, 
            Sales[Material number 10-digit] = EARLIER(Sales[Material number 10-digit])
        )
    )
mxix
  • 3,539
  • 1
  • 16
  • 23