Only the highest invoice number per project should be taken into account

[Meeresblume]

Hi there,

I have a table in Power BI. This table contains project numbers, clerks, invoice numbers, amount and a date.
There can be several invoice numbers with different date for a project.

Now I would like to create a report that contains 2 data slicers. Once for the date (from-to) and once for the clerk.
In addition, the amount should be totaled out.

So far no problem. However, I would like only the highest invoice number per project to be included in the total.

Here's an example:

When filtering by date January 01, 2021 and clerk A
Result: 80 (invoice number 1 and 8 )
When filtering by date January 01, 2021 and clerk B
Result: 15 (invoice number 4)
When filtering by date January 02, 2021 and clerk A
Result: 25 (ONLY invoice number 3 and 7, NOT also number 2)
When filtering by date January 02, 2021 and clerk B
Result: 10 (ONLY invoice number 6, NOT also number 5)

Hope it's understandable 🙂

How exactly can I ensure that only the highest invoice number per project is taken into account in the amount, in addition to the filtered date and person responsible?

thank you for your help 🙂

LG

[amitchandak]

@Meeresblume , Try a measure like this with project number and date in visual

Measure =
VAR __id = MAX ('Table'[Project Number] )
VAR __date = CALCULATE ( MAX('Table'[Date] ), ALLSELECTED ('Table' ), 'Table'[Project Number] = __id )
CALCULATE ( Sum ('Table'[Amount] ), VALUES ('Table'[Project Number] ),'Table'[Project Number] = __id,'Table'[Date] = __date )

[Meeresblume]

@amitchandak  

Thanks for your answer.
However, the measure would not work.

I need the highest offer number per project, but depending on my filter with the date and the person responsible, the amount that is totaled.

The way I interpret the measure from you, I would only make this out by attaching the project number and the max date. But not from my filtered date + clerk.

[v-easonf-msft · Accepted solution]

Hi, @Meeresblume 

Try  formula as below, it may be a bit clumsy:

amount of max invoice number = 
VAR _max_invoice_number =
    CALCULATE (
        MAX ( 'Table'[invoice number] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Project number] = MIN ( 'Table'[Project number] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[amount] ),
        FILTER ( 'Table', 'Table'[invoice number] = _max_invoice_number )
    )

Result = 
VAR tab =
    SUMMARIZE (
        'Table',
        'Table'[Project number],
        "s_amount", [amount of max invoice number]
    )
RETURN
    SUMX ( tab, [s_amount] )

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason