Forum Discussion
Helper IDelta between values of same column but different rows
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
zervino Try:
vs Actuals Measure = VAR __Sales = MAX( 'Table'[Sales] ) VAR __Actuals = MAXX( FILTER( ALL( 'Table', [Plan] = "Actuals" ), [Sales] ) VAR __Result = __Sales - __Actuals RETURN __ResultOoh, look, a Calculate 🙂
vs Actuals = sum('Table'[Sales])-CALCULATE(sum('Table'[Sales]),'Table'[Plan]="Actuals")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.
6 Replies
- Greg_Deckler
Community Champion
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
Super User
Ooh, look, a Calculate 🙂
vs Actuals = sum('Table'[Sales])-CALCULATE(sum('Table'[Sales]),'Table'[Plan]="Actuals")
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.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.
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.