Forum Discussion
Calculate delta on matrix visual
I am using Matrix visual to display the sales conversion % by year group. The sales conversion from a calculated measure. Year group comes from grouping function on Year
Matrix visual gives Total of the row, but in my case, I need to see the difference (delta) between 2 groups of year as below:
| Year (groups) 2 | Sales Conversion |
| 2017 & 2018 | 95.7% |
| 2019 & 2020 | 93.7% |
| Difference | 2.0.% |
I thought of using DAX function EARLIER but it is stated that EARLIER cannot be used in measure, it is mostly used in calculated column.
Has anyone tried compute delta (difference) in Matrix visual?
Any help is appreciated.
Hi Anonymous
May be you like this way also..Create a index column by dax
index = RANKX('Table',[Year (groups)],,ASC)
then calculate the previous value measure by considering this index column
VAR _PrevValue=CALCULATE(SUM('Table'[Sales conversion]),FILTER(ALL('Table'),'Table'[index] = MAX('Table'[index])-1) )VAR Result = IF(ISBLANK(_PrevValue),BLANK(),_PrevValue- SUM('Table'[Sales conversion]) )returnResultHope it helpsRegards,sanalyticsYou can use a measure like this to do that. In the total row, there are multiple year group values, so an IF can be used to return a different calculation there. This measure will return the difference between the max and min values for the year groups.
Sales Conversion New =
VAR vSummary =
ADDCOLUMNS (
DISTINCT ( YearGroupDifference[Year (groups) 2] ),
"cSum", [SalesConversion]
)
VAR vMax =
MAXX (
vSummary,
[cSum]
)
VAR vMin =
MINX (
vSummary,
[cSum]
)
RETURN
IF (
COUNTROWS ( YearGroupDifference ) = 1,
vMax,
vMax - vMin
)You can also rename Total to Difference.
Regards,
Pat
Hi Anonymous,
In fact, the table/matrix visual normally displays the default aggregate mode 'sum' on the total level without any other specific filters.
mahoneypat 's solution is to modify the total level of table/matrix title to 'difference' and writes the measure formula to check the total row content level and show the corresponding value.
Clever Hierarchy Handling in DAX
For this scenario, you need to manually modify the total level name to change the 'total' to 'difference'. (in format tab -> 'total'/'subtotal' options)
Regards,
Xiaoxin Sheng
5 Replies
- Nathaniel_C
Community Champion
Hi Anonymous ,
Working with the data you provided, created a table and pivoted it in Power Query, then applied a measure for the delta.
delta = MAX('Table (3)'[2017 & 2018])-MAX('Table (3)'[2019 & 2020])
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
If you would share your pbix, or dummy up some values in Excel both for current and expected data. Please copy and paste them into your post, rather than doing a picture, we may be able to help you.
Please read this post to get your question answered more quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490 
sanalyticsSuper User
Hi Anonymous
May be you like this way also..Create a index column by dax
index = RANKX('Table',[Year (groups)],,ASC)
then calculate the previous value measure by considering this index column
VAR _PrevValue=CALCULATE(SUM('Table'[Sales conversion]),FILTER(ALL('Table'),'Table'[index] = MAX('Table'[index])-1) )VAR Result = IF(ISBLANK(_PrevValue),BLANK(),_PrevValue- SUM('Table'[Sales conversion]) )returnResultHope it helpsRegards,sanalytics- mahoneypat
Microsoft Employee
You can use a measure like this to do that. In the total row, there are multiple year group values, so an IF can be used to return a different calculation there. This measure will return the difference between the max and min values for the year groups.
Sales Conversion New =
VAR vSummary =
ADDCOLUMNS (
DISTINCT ( YearGroupDifference[Year (groups) 2] ),
"cSum", [SalesConversion]
)
VAR vMax =
MAXX (
vSummary,
[cSum]
)
VAR vMin =
MINX (
vSummary,
[cSum]
)
RETURN
IF (
COUNTROWS ( YearGroupDifference ) = 1,
vMax,
vMax - vMin
)You can also rename Total to Difference.
Regards,
Pat
Hi Anonymous,
In fact, the table/matrix visual normally displays the default aggregate mode 'sum' on the total level without any other specific filters.
mahoneypat 's solution is to modify the total level of table/matrix title to 'difference' and writes the measure formula to check the total row content level and show the corresponding value.
Clever Hierarchy Handling in DAX
For this scenario, you need to manually modify the total level name to change the 'total' to 'difference'. (in format tab -> 'total'/'subtotal' options)
Regards,
Xiaoxin Sheng
