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

[Greg_Deckler · Accepted solution]

@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 · Accepted solution]

Ooh, look, a Calculate 🙂

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

[v-hjannapu · Accepted solution]

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.

[v-hjannapu]

Hi @zervino,

Just wanted to check if you had the opportunity to review the suggestion provided?

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

Thank You.

Harshitha.

[v-hjannapu]

Hi @zervino  ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[v-hjannapu]

Hi @zervino,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please Accept it as a solution so that other community members can find it easily.

Thank you.

[Greg_Deckler · Accepted solution]

@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 · Accepted solution]

Ooh, look, a Calculate 🙂

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