Forum Discussion

j3n5k1's avatar
j3n5k1
New Member
3 years ago
Solved

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 creat...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

lbendlin's avatar
lbendlin
Super User
3 years ago

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)

j3n5k1's avatar
j3n5k1
New Member
3 years ago

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