topic Request Assistance:DAX Formula to Calculate Surplus based on differing contract minimums by location in DAX Commands and Tips

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

KMGC — Wed, 13 Mar 2024 16:10:40 GMT

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!

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

Anonymous — Thu, 14 Mar 2024 02:27:17 GMT

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.

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

KMGC — Thu, 14 Mar 2024 12:02:27 GMT

Works perfectly. Thanks!