Request Assistance:DAX Formula to Calculate Surplus based on differing contract minimums by location

[KMGC]

Hi all,

Been looking and couldn't find anything that would help me with this one. I have different locations with different contract minimums. I have a dax measure for total sales and then I want to calculate the revenue surplus based on the contract minimum for a location. Essentially subtracting the location-specific contract minimum amount from the total sales amount for that location. 

I have looked in a bunch of places to recreate a 'nested if' and I'm stumped. Here's the current table:

The 'Revenue Surplus' measure being used right now just takes the total sales measure and subtracts $1,000. I need to be able to change that amount based on the location. All of the data is in one table and the only measures from that table are 'Total Sales' and 'Revenue Surplus.'

'Total Sales'= SUM(Sales)

'Revenue Surplus'= [Total Sales]-1000 (this is the one I need to change)

Any help would be appreciated. Thank you!

[KMGC]

Works perfectly. Thanks!

[Anonymous · Accepted solution]

Hi @KMGC 

Please try this:

First of all, I create a set of sample:

And you need to create a new table to store contract minimums:

Then create a 1:* relationship between the two tables:

Then add 2 measure:

TOTAL = SUM('Table'[Total Sales])

Revenus =
[TOTAL]
    - CALCULATE (
        MAX ( 'Table (2)'[contract minimums] ),
        'Table (2)'[Location] = SELECTEDVALUE ( 'Table'[Location] )
    )

The result is as follow:

Best Regards

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