IF Function

[marsclone]

I'm struggling with (i think) an easy measure.

But can't get it to work.

I have an open item list from my customers.

I have a list with max limits for my customers.

I want to create a matrix which shows the total amount per customer and if the total amount exceeds the limit it must show the shortfall of this position.

Open item list
Customer	Invoice	Amount
A	201901	€ 250
A	201902	€ 350
B	201903	€ 600
B	201904	€ 400
B	201905	€ 200
C	201906	€ 390
C	201907	€ 250
C	201908	€ 600
C	201909	€ 800
		€ 3.840

Limitlist
Customer	Limit
A	€ 800
B	€ 1.000
C	€ 2.000

Total list
Customer	Amount	Limit	Shortfall
A	600	800	0
B	1200	1000	-200
C	2040	2000	-40
	3840		-240

Which measure do i need for this?

Thank you in advance

[Zubair_Muhammad · Accepted solution]

@marsclone 

Try this MEASURE

see attached file as well

Measure =
IF (
    HASONEFILTER ( LimitList[Customer] ),
    IF (
        SUM ( Open_item_list[Amount] ) > SUM ( LimitList[Limit] ),
        SUM ( LimitList[Limit] ) - SUM ( Open_item_list[Amount] ),
        0
    ),
    SUMX (
        VALUES ( LimitList[Customer] ),
        CALCULATE (
            IF (
                SUM ( Open_item_list[Amount] ) > SUM ( LimitList[Limit] ),
                SUM ( LimitList[Limit] ) - SUM ( Open_item_list[Amount] ),
                0
            )
        )
    )
)

[marsclone]

That worked really well!

So it must be in table form and not matrix?

Thank you!