Forum Discussion
variance between columns in a matrix
Good Morning All!
I have been able to create cool things on PowerBi. However, I am struggling trying to get a variance between values on columns.
I have two columns (see below) with the most critical information, one is Year and the other one is sales.
However, I am not able to create a variance between the values of one year of the other which are displayed in different columns.
I would be more than happy if anyone can give me some help with the topic!!!!
My data in on the following format:
| Year | Brand | Line | Sales |
| 2016 | A | Boxes | 4931.4 |
| 2015 | A | Boxes | 14326.0 |
| 2014 | A | Boxes | 15179.6 |
| 2016 | B | Boxes | 7575.1 |
| 2015 | B | Boxes | 12087.0 |
| 2014 | B | Boxes | 1659.0 |
And I have been able to reproduce a matrix with values and weight over the total as follows:
| Boxes | 2016 | % Weight | Variance | 2015 | % Weight | Variance | 2014 | % Weight |
| A | 4931.4 | 39.4% | Needed Calculation | 14326.0 | 54.2% | Needed Calculation | 15179.6 | 90.1% |
| B | 7575.1 | 60.6% | Needed Calculation | 12087.0 | 45.8% | Needed Calculation | 1659.0 | 9.9% |
| Total | 12506.5 | 100.0% | Needed Calculation | 26413.0 | 100.0% | Needed Calculation | 16838.6 | 100.0% |
But I am obivously missing the fact that I could calculate A 2016 number against 2015 value, and so for the other item!
Thank you and merry Xmas everyone!!
Do I need to create an additional table with the Years and create relationships? Any help would do great. :)
6 Replies
- kcantor
Community Champion
You would be best served using a data dimension table and creating a second measure for Previous year. The you would use two measure to find the variance. For example in sales you might have:
Total Sales = SUM(Sales[LineSales])
Last year Sales = CALCULATE([Total Sales], DATEADD(DimDate[DateKey], -1, year)
Variance = [Total Sales]-[Last year Sales]
Of course going this route would requre you to drop the year from the columns and list your dates as rows using the measures as values.
Hi kcantor Thank you very much for the guidance.
So far I was not sure how to create a dimension table, but finally I managed to create them,
I started by creating the table, and then assigning a dimension to the year as shown below:
. I started by establishing a values on the following format and also created a timestamp to define a year, as that is the only measure I need.
Can you please confirm how to create the relationship between both tables?
Now, when it comes to create the variance on the main table, I am not sure what sort of code should I follow, I tried yours but I am getting the following errorr on triying to display last year sales
I am creating a Matrix table to show the information, not sure what I am missing here.
Thank you very much!!!
- parry2k
Super User
juanmobando you are setting relationship between year and date , both are different data type.
Add a calculated column in your table to create a date and set relation on that column:
- add column DummyDate (or name whatever you want)
DummyDate = Date(Table1[Year], 1,1)
- go to modelling tab and set data type to Date
- set relation on dummydate with your date dimention on date
v-jiascu-msftMicrosoft Employee
Hi juanmobando,
Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.
Best Regards!
DaleHi Dale,
Thanks for the help.
But I am afraid I cannot even create the first calculatio for the year 2014
CALCULATE ( SUM ( Table1[Sales] ), Table1[Year] = 2014 )
When I am creating the measure, I can only get to the first expression, but Bi does not allow me to enter the Table1[Year] expression...
Therefore I cannot go any forward with the solution.
Is there something I need to enable on the formulae or any options that I am not looking at that would be helpful? I am afraid that there is might be an issue of the syntax, because I tried but it does not allow me enter other field on the expression..
Please let me know.
Thank you!
