cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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) 2Sales Conversion
2017 & 201895.7%
2019 & 202093.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.

 

3 ACCEPTED SOLUTIONS
Responsive Resident
Responsive Resident

Hi @MikeKK 

 

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

View solution in original post

Super User IV
Super User IV

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
    )

 

mahoneypat_0-1606585485302.png

You can also rename Total to Difference.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Hi @MikeKK,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
Super User IV
Super User IV

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
    )

 

mahoneypat_0-1606585485302.png

You can also rename Total to Difference.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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) 2Sales Conversion
2017 & 201895.7%
2019 & 202093.7%
Difference 2.0.%

 

Hi @MikeKK,

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

Responsive Resident
Responsive Resident

Hi @MikeKK 

 

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

View solution in original post

Super User I
Super User I

Hi @MikeKK ,

Working with the data you provided, created a table and pivoted it in Power Query, then applied a measure for the delta.

 

del1.PNG

 

del2.PNG

 

 

delta = MAX('Table (3)'[2017 & 2018])-MAX('Table (3)'[2019 & 2020])

del.PNG


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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors