Forum Discussion
Matrix table calculations
My apologies if asking a repeated question. Couldn't find an answer after lots of reading.
I created a matrix table for field table 'Productivity' where rows on the matrix are names of employees from field column [name] and columns on the matrix show the year hierarchy derived from field column [date]; so when running the matrix it shows three columns' 2021,2022,2023 and more year columns in the future. The Value of the matrix is a Field Parameter [productivity.fields] of three field columns; [field1],[field2]and [field3]. All I need is to create a new column on the matrix to calculate % difference in value beteen the last two years on the table (column 2023 compared to column 2022) provided that when 2024 data comes out, it will do so for 2024 compared to 2023. Thank you in advance for any help you could provide.
had some issues posting the table and matrix but in any case, I finally found the answer:
Field1 % Var =var _a = CALCULATE(SUM(Productivity[Field1),FILTER(Master,Master[Date].[Year]=max(Master[Date].[Year])-1))var _b = CALCULATE(SUM(Productivity[Field1]),FILTER(Master,Master[Date].[Year]=max(Master[Date].[Year])))returnIF(ISINSCOPE(Master[Date].[Year]),SUM(Productivity[Field1]),FORMAT(DIVIDE(_b-_a,_a),"Percent"))
2 Replies
Hi NBDOC ,
In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?
Thanks for your efforts & time in advance.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.had some issues posting the table and matrix but in any case, I finally found the answer:
Field1 % Var =var _a = CALCULATE(SUM(Productivity[Field1),FILTER(Master,Master[Date].[Year]=max(Master[Date].[Year])-1))var _b = CALCULATE(SUM(Productivity[Field1]),FILTER(Master,Master[Date].[Year]=max(Master[Date].[Year])))returnIF(ISINSCOPE(Master[Date].[Year]),SUM(Productivity[Field1]),FORMAT(DIVIDE(_b-_a,_a),"Percent"))
