Delta between values of same column but different rows

[zervino]

I have a table like this:

Plan	Month	Customer	Sales
Budget	5	ABC	100
Actuals	5	ABC	50
Forecast	5	ABC	130

I want to create a measure that compares always against the "Actuals" of the same month and customer:

Plan	Month	Customer	Sales	vs Actuals
Budget	5	ABC	100	50
Actuals	5	ABC	50	0
Forecast	5	ABC	130	80

How can I achieve this?

Thanks

[v-hjannapu]

Hi @zervino,

Thank you  for reaching out to the Microsoft fabric community forum.
Thank you @lbendlin, for you reply regarding the issue.

By creating this Dax measure you may achieve :

vs Actuals = 
VAR currentMonth = SELECTEDVALUE('SalesData'[Month])
VAR currentCustomer = SELECTEDVALUE('SalesData'[Customer])
VAR actualSales = 
    CALCULATE(
        SUM('SalesData'[Sales]),
        'SalesData'[Plan] = "Actuals",
        'SalesData'[Month] = currentMonth,
        'SalesData'[Customer] = currentCustomer
    )
RETURN
IF(SELECTEDVALUE('SalesData'[Plan]) = "Actuals", 0, SUM('SalesData'[Sales]) - actualSales)

I tested it with  sample data, and it working fine. Please find the attached screenshot and Pbix for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Harshitha.

[Greg_Deckler]

@zervino Try:

vs Actuals Measure = 
  VAR __Sales = MAX( 'Table'[Sales] )
  VAR __Actuals = MAXX( FILTER( ALL( 'Table', [Plan] = "Actuals" ), [Sales] )
  VAR __Result = __Sales - __Actuals
RETURN
  __Result

[lbendlin]

Ooh, look, a Calculate 🙂

vs Actuals = sum('Table'[Sales])-CALCULATE(sum('Table'[Sales]),'Table'[Plan]="Actuals")