Calculate delta on matrix visual

[Anonymous]

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.

[sanalytics · Accepted solution]

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]) )

return

Result

Hope it helps

Regards,

sanalytics

[mahoneypat · Accepted solution]

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

[mahoneypat · Accepted solution]

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

[Anonymous]

Thank you , The solution gives a meaure of the sales conversion delta, it still does not give a matrix/table display of difference at the bottom. I know this could be the limitation of the visual.

Year (groups) 2	Sales Conversion
2017 & 2018	95.7%
2019 & 2020	93.7%
Difference	2.0.%

[v-shex-msft · Accepted solution]

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

[sanalytics · Accepted solution]

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]) )

return

Result

Hope it helps

Regards,

sanalytics

[Nathaniel_C]

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