Forum Discussion
Difference and Variance between 2 columns in a Matrix
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,
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,
3 Replies
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,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.