Calculate Sales over budget

[Anonymous]

Hi all,

I have a table called Sales with columns Fiscal Year, Fiscal Quarter, Region, Country, Partner, Budget, and Sales.
I wanted to create a measure that sums (Sales - Budget) by Partner, only when Sales > Budget (“UpSales”). I will then filter the other columns in a pivot table.

I tried to create something like this so I will be able to sum the “UpSales” by Country and Region.
CALCULATETABLE(
SUMMARIZE ( Sales,
Sales[Partner],
“UpSales” , [Sales] - [Budget]
) [Sales] > [Budget] — here I got the error " CALCULATE’ has been used in a True/False expression]" —

Here is a sample of the table and the expected result. Upsell 

Thanks in advance!

[Anonymous]

@amitchandak , thank you for your help!

[amitchandak · Accepted solution]

@Anonymous , Create a measure

M1=

if([Sales] - [Budget] >"Upsales", ""Down Sales")

Create a new table with two values upsales and Downsales , say Bucket

Now create new measures

new Measure
Sumx(filter(Values(sales[Partner]), [M1] = max(bucket[Value])), [Sales])

Make sure bucket[Value] is there in visual too

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ