topic Re: Calculate Sales over budget in DAX Commands and Tips

Calculate Sales over budget

Anonymous — Fri, 13 May 2022 06:26:46 GMT

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!

Re: Calculate Sales over budget

amitchandak — Fri, 13 May 2022 06:30:35 GMT

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

Re: Calculate Sales over budget

Anonymous — Fri, 13 May 2022 07:55:16 GMT

amitchandak , thank you for your help!