Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Meeresblume
Helper I
Helper I

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

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:

Meeresblume_0-1638834961628.png

 

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

1 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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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 )

@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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.