Calculate Sales over budget

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
Upsell.xlsx (15.8 KB)
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]" —

I am sending a sample of the table and the expected result.

Thanks in advance!

Hi @cesartozzi - For your requirement, try a simple formula like below.

Measure 2 = sumx(filter(Test, Test[Sales] > Test[Budget]),Test[Sales] - Test[Budget])

Thanks
Ankit J

1 Like

Hi @cesartozzi, did the response provided by Ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

Hi @ankit, thank you for your reply. I found another solution that worked well:

Create a measure

M1= if([Sales] > [Budget],“Upsales”, "“Down Sales”)

Create a new table with two values Upsales and Downsales , named Bucket.

Now create new measures

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