variance between columns in a matrix

[juanmobando]

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. 🙂

[v-jiascu-msft]

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!
Dale

[juanmobando]

Hi 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!

[kcantor]

@juanmobando

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. 

[juanmobando]

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!!!

[v-jiascu-msft]

Hi @juanmobando,

Is this case the same with this one of yours? I have replied. Please try it out.

Best Regards,

Dale

[parry2k]

@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