Summary using latest quotes

DAX DAX Calculations
1

Hi

I want to create a summary table based on product of all OPEN opportunities. An Opportunity can have multiple quotes associated with it and each quote can have multiple lines.

The value of the Opportunity is to be calculated from the latest quote (derived from QUO_CreateDate)

How can I achieve this? I’ve attached some dummy data.
I’ve got the latest quote value when in the context of OP_Id but can’t work out how to translate this to ProductName context.

Also the totalsDummy Data File 2.pbix (153.8 KB) for the table on Latest Quote doesn’t total the column

2

This is what I have so far

LastQuote = 
SUMX( TOPN(1, CALCULATETABLE(
    SUMMARIZE( 'Opportunity Quotes','Opportunity Quotes'[QUO_CreateDate]),ALL('Item Master'[ProductName])),
        'Opportunity Quotes'[QUO_CreateDate],DESC),
        [Quote £])

It appears to work but the Total column is incorrect - how can I fix that?

image

3

Seem to work for me

image
image891×438 23.3 KB

I’m using exactly the same formula.

Have you got some filter or slicer on the table?

Sam

4

That’s strange.

Your result isn’t the same as mine in that it is bringing through all the quotes not just the latest one. See the negotiation column as an example.

That table originally was using a different measure could that be why we aren’t getting the same result?