Difference and Variance between 2 columns in a Matrix

[Anonymous]

I have looked at different solutions to this, but none have worked for me:

I have a martix with the total number of forms per year for each row. I want to add a column that calculates the difference per year by row, and also a column that lists the variance by row. 

Example:

Source     2016     2017     Difference      Variance

_______     ______   _______   ___________      ___________

eCom     40          25         - 15                   -46.2% 

ad          20          30            10                       40%

email     10          45            35                   127.3% 

I currently have this:

What measurements/calculated columns should I be creating to get the desired results?

[Anonymous]

Hi @Anonymous

Do you have a calendar dimension aka data table in use?

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Do you have source table as shown in the following screenshot?


If so, create a new table using DAX below.

Table = SUMMARIZE(SourceTable,SourceTable[Source],SourceTable[Year],"Value",SUM(SourceTable[Value]))

Then create the following columns in the new table and create a Matrix visual.

Previous = CALCULATE(MAX('Table'[Value]),FILTER('Table','Table'[Source]=EARLIER('Table'[Source])&& 'Table'[Year]<EARLIER('Table'[Year])))
diff = IF(ISBLANK('Table'[Previous]),0, 'Table'[Value]-'Table'[Previous])

Besides, what logic do you use to calculate Variance?

Regards,

[jimcoleman]

Lydia Zhang

Having the same issue, and I have it down to what is shown in your output.  But I do not want the diff to been shown with what you have as 2016 data.  I have many columns to show and the additional columns are not required.

Currently I have sales data for 2017, 2018 and 2019.  I want to show the difference between 2017 and 2018 which I have a measure for and it's working correctly.  However that measure shows up under 2017, 2018 and 2019 and is only required under 2018.

This seems like something that should be so easy, yet seems so difficult.

BTW very new to Power BI so I must be missing some easy trick.

JC.