CALCULATE A VALUE GROUPING FROM OTHER TABLE

[ancorte]

Hi, i've a fact table with Product Quantity Price, Customer, and so on. I have a table with Product, and another with Customer.

I try to have a measure that give me the Value of all product sold, when the total quantity of product sold (for all customers) is less than a certain value.

Tha fact tabel has this columns:

Customer, Date, CodeArticle, UnitPrice, Quantity, Total Price

I tried with this measure

CALCULATE(SUM(FT[PrezzoTotale]),

FILTER( FT, SUM(FT[Quantita])<100))

and if this measure is in the table with the Products(on the rows) it's all ok: but i want only a total value.

[v-yanjiang-msft · Accepted solution]

Hi @ancorte ,

According to your description, if CodeArticle in fact table represents product, here's my solution.

Here's my sample.

Create a measure.

Measure =
SUMX (
    FILTER (
        'FT',
        SUMX (
            FILTER ( 'FT', 'FT'[CodeArticle] = EARLIER ( FT[CodeArticle] ) ),
            'FT'[Quantity]
        ) < 100
    ),
    'FT'[Total Price]
)

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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