Calculations between 2 matrix

[Shackwell]

Hello fine gentlemen of the web,

Currently I have the following scenario. I have 2 matrix in a report page, where the rows are the departments and for columns I have the 12 months of the year. One matrix is from 2020 and the other is from 2021. Im trying to show a third matrix with calculations of variance and the monetary difference as well. Any jelp would be appreciated. 

[Shackwell]

Yes this is great to get me started. I just have a question for one of the strings of the formula. =Calculate(sum(FACTS). What does the Fact operation have to do in here? I think I understood the rest without issues.

[negi007]

@Shackwell 

if you want sum of values for a year or basis some critria, you use calculate function and second argumnet is the filter criteria like below. facts is basically is the name of the table and value is the column which is being used for sum

2021_Val = calculate(SUM(facts[value]),facts[year]=2021)

if you just need the sum of values from facts table, then you can use simply sum(facts[value])

[negi007]

@Shackwell will it be possible for you to share some dummy sample data with the expected output. this will help you in a better way.

[Shackwell]

So here in the sample data you can see the first 2 matrix being filled with departments as rows and months as columns. I want to create a calculation between matrix, having the 3rd matrix doing a substraction between and the 4th matrix to show a percentage change in the given period. Im not sure if you wanted to see the desired output or you wanted to see how is data being added? thanks again,

 

[negi007 · Accepted solution]

@Shackwell in this case, you simply need to create few measures like below

2021_Val = calculate(SUM(facts[value]),facts[year]=2021)

2020_Val = calculate(SUM(facts[value]),facts[year]=2020)

Variance = facts[2021_Val]-facts[2020_Val]

variance% = facts[2021_Val]/facts[2020_Val]-1

then simily put these measures in your matrix and you get below visual 

also since for calc variance we would need date as well so i have created date basis month as well like first day of each month

i am also attaching pbix file for your reference

i hope it help you 

[Shackwell]

Hello there,

We are almost there. I have managed to get the measures going but I have encountered the following. There might be some spaces with 0 number allocation, so whenever Im trying to divide I get that the result is infinite. Also there's a month before January in the report marked as blanks but still shows calculation within the departments.