Forum Discussion
Delta between two matrix
Hello,
I've been searching for hours, but maybe cause of my issue description, I can't find a solution to it.
Unfortunately, I can't share a proper file, but I hope the table below would help.
Let's say I have a table as:
Out of that table, I would make a matrix with some filters, like that:
1)If I want to duplicate this matrix as Matrix2, and find the delta between Matrix 1 & Matrix 2, do I have to duplicate Table 1 in order to dissociate Matrix 1 from 2? From what I found so far, it seems yes, but I just want to make sure.
Then, let's say I already duplicate Table 1 and created an other Matrix based on Table 2:
2)How do I create a third matrix with the delta between 1 & 2?
3)From which table rows and colums need to be selected? Do I need to create a third table with those informations and related to Table1 and 2?
FYI: what i've tried till now:
-Duplicate table 1 to table 2. Have Matrix 1 (Table1) and Matrix 2 (Table2).
-Link table 1 to table 2 per bracket (don't ask me why, I've tried to see if it could work, I did get a number making sense - I'm conscious it might be wrong).
-Used CALCULATE(SUM(ALLSELECTED(All the filters above). I could get the difference on the grand total in one of my try, but not per rows/columns.
Let me know if you need anything else,
Thank you for your help (hope it's possible)
Nicolas
Alright, I'm gonna solve my own issue, but in case someone need the same thing, I put the resolution I found that work (might not be the best option of course).
I duplicate Table1 to get Table2.
I create 2 new table: one with Bracket, one with Category, both link to Table 1 & 2 (WIll be the rows and columns for Matrix 3).
I did the 2 matrix from Table 1 and Table 2 with their associated filters.
To get the difference:
DELTA=CALCULATE(SUM(Table1[QUANTITY],ALLSELECTED(Table1[YEAR],Table1[REGION],Table1[CLIENT]))-CALCULATE(SUM(Table2[QUANTITY],ALLSELECTED(Table2[YEAR],Table2[REGION],Table2[CLIENT]))
I put those value in Matrix 3 with Row = Table3[BRACKET] and Columns = Table4[CATEGORY]
And it worked..so far 🙂
Nicolas
1 Reply
Alright, I'm gonna solve my own issue, but in case someone need the same thing, I put the resolution I found that work (might not be the best option of course).
I duplicate Table1 to get Table2.
I create 2 new table: one with Bracket, one with Category, both link to Table 1 & 2 (WIll be the rows and columns for Matrix 3).
I did the 2 matrix from Table 1 and Table 2 with their associated filters.
To get the difference:
DELTA=CALCULATE(SUM(Table1[QUANTITY],ALLSELECTED(Table1[YEAR],Table1[REGION],Table1[CLIENT]))-CALCULATE(SUM(Table2[QUANTITY],ALLSELECTED(Table2[YEAR],Table2[REGION],Table2[CLIENT]))
I put those value in Matrix 3 with Row = Table3[BRACKET] and Columns = Table4[CATEGORY]
And it worked..so far 🙂
Nicolas