Forum Discussion
Difference between 2 columns
I have a matrix like the below where I have swapped the values to rows. What I would like is to create another column showing the difference between Current and Prior Year for all metrics. If I create them individually they go below the other metrics and I want to see this as a column like below.
Is there a way of doing this?
| Prior Year | Current Year | Difference | |
| Growth | |||
| Cancels | |||
| Retention % |
Hi j3n5k1 ,
Please try:
First add a new column to the table:
Then creaet a new table:
Manage relationship:
Apply the measure:
Sum of Cancles = var _a = FORMAT( CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data'[Year])),"General Number") var _b = CALCULATE(SUM('Dummy Data'[Cancels]),FILTER('Table',[Year]=_a)) var _c = CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data')) var _d = CALCULATE(SUM('Dummy Data'[Cancels]),FILTER(ALL('Table'),[Year]=FORMAT(_c,"General Number"))) var _e = CALCULATE(SUM('Dummy Data'[Cancels]),FILTER(ALL('Table'),[Year]=FORMAT(_c-1,"General Number"))) return IF(MAX('Table'[Year])="Difference",_d-_e,_b) Sum of Growth = var _a = FORMAT( CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data'[Year])),"General Number") var _b = CALCULATE(SUM('Dummy Data'[Growth]),FILTER('Table',[Year]=_a)) var _c = CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data')) var _d = CALCULATE(SUM('Dummy Data'[Growth]),FILTER(ALL('Table'),[Year]=FORMAT(_c,"General Number"))) var _e = CALCULATE(SUM('Dummy Data'[Growth]),FILTER(ALL('Table'),[Year]=FORMAT(_c-1,"General Number"))) return IF(MAX('Table'[Year])="Difference",_d-_e,_b) Sum of Transacted = var _a = FORMAT( CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data'[Year])),"General Number") var _b = CALCULATE(SUM('Dummy Data'[Transacted $]),FILTER('Table',[Year]=_a)) var _c = CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data')) var _d = CALCULATE(SUM('Dummy Data'[Transacted $]),FILTER(ALL('Table'),[Year]=FORMAT(_c,"General Number"))) var _e = CALCULATE(SUM('Dummy Data'[Transacted $]),FILTER(ALL('Table'),[Year]=FORMAT(_c-1,"General Number"))) return IF(MAX('Table'[Year])="Difference",_d-_e,_b) Retention % = DIVIDE([Sum of Cancles], [Sum of Transacted]) NCVI = [Sum of Growth]-[Sum of Cancles]Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
the "Prior Year" column is redundant. Remove that. For "Difference" add a standard YoY or YoY% measure. (You can create these via quick measures too)
This won't solve my issue - I have multiple metrics as rows and a YoY difference as you described will only impact one of them, plus this will then show as another metric (so go on the rows in how i have created the view), not as a new column.
I need the matrix to look as described in my question so the PY field is not redundant, it is necessary
Hi j3n5k1 ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.
Refer to:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I have measures (growth, cancels, retention % etc) which are all normally showing as columns (values) in a matrix on PBI with date etc as rows.
For this particular view i have used the "switch values to rows" so my table looks as described.
If I was doing this in excel, I would now have my dates at the top, measures down the side - and a simple C-B to create a new column showing the difference for each of those values for each of those date ranges. I just don't know if that is possible? I can't use the source file as it is all confidential, but I have created a simple file to show what I mean - Dummy Report
Hi j3n5k1 ,
Please try:
First add a new column to the table:
Then creaet a new table:
Manage relationship:
Apply the measure:
Sum of Cancles = var _a = FORMAT( CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data'[Year])),"General Number") var _b = CALCULATE(SUM('Dummy Data'[Cancels]),FILTER('Table',[Year]=_a)) var _c = CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data')) var _d = CALCULATE(SUM('Dummy Data'[Cancels]),FILTER(ALL('Table'),[Year]=FORMAT(_c,"General Number"))) var _e = CALCULATE(SUM('Dummy Data'[Cancels]),FILTER(ALL('Table'),[Year]=FORMAT(_c-1,"General Number"))) return IF(MAX('Table'[Year])="Difference",_d-_e,_b) Sum of Growth = var _a = FORMAT( CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data'[Year])),"General Number") var _b = CALCULATE(SUM('Dummy Data'[Growth]),FILTER('Table',[Year]=_a)) var _c = CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data')) var _d = CALCULATE(SUM('Dummy Data'[Growth]),FILTER(ALL('Table'),[Year]=FORMAT(_c,"General Number"))) var _e = CALCULATE(SUM('Dummy Data'[Growth]),FILTER(ALL('Table'),[Year]=FORMAT(_c-1,"General Number"))) return IF(MAX('Table'[Year])="Difference",_d-_e,_b) Sum of Transacted = var _a = FORMAT( CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data'[Year])),"General Number") var _b = CALCULATE(SUM('Dummy Data'[Transacted $]),FILTER('Table',[Year]=_a)) var _c = CALCULATE(MAX('Dummy Data'[Year]),ALL('Dummy Data')) var _d = CALCULATE(SUM('Dummy Data'[Transacted $]),FILTER(ALL('Table'),[Year]=FORMAT(_c,"General Number"))) var _e = CALCULATE(SUM('Dummy Data'[Transacted $]),FILTER(ALL('Table'),[Year]=FORMAT(_c-1,"General Number"))) return IF(MAX('Table'[Year])="Difference",_d-_e,_b) Retention % = DIVIDE([Sum of Cancles], [Sum of Transacted]) NCVI = [Sum of Growth]-[Sum of Cancles]Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
