Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to Solution.
@Tozzi , 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
@Tozzi , 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
User | Count |
---|---|
51 | |
48 | |
20 | |
16 | |
15 |
User | Count |
---|---|
110 | |
45 | |
42 | |
24 | |
20 |