Hi All,
Can we add a new columns (not calculated) into a matrix? That said, we wish to add a new column based on a summarized data. Is this doable?
What about saving a matrix as a table if the above suggestion is not workable and available at the moment? (**without exporting and re-importing the data)
Example of the raw data, Table:
Date | Year | Qtr | Period | Customer | Sales |
1/12/2018 | 2018 | 1 | 1 | A | 100 |
5/12/2019 | 2019 | 1 | 1 | A | 200 |
5/2/2018 | 2018 | 1 | 3 | B | 400 |
5/2/2018 | 2018 | 1 | 3 | B | 400 |
5/2/2019 | 2019 | 1 | 3 | B | 300 |
Table1 (Summarized from the example of raw date above):
Year | Period | Customer | Sum of Sales | Commissions Category |
2018 | 1 | A | 100 | |
2018 | 3 | B | 800 | |
2019 | 1 | A | 200 | |
2019 | 3 | B | 300 | |
I figured out that we can use “Summarized” function to create a new matrix-like table (similar as per Table1) as well where below is the expressions:
Summarized = SUMMARIZE (Table,Table[Year],Table[Period],Table[Customer],”Sum of Sales”,sum(‘Table’[Sales]))
Desired output:
Year | Period | Customer | Sum of Sales | Commissions Category |
2018 | 1 | A | 100 | B |
2018 | 3 | B | 800 | A |
2019 | 1 | A | 200 | B |
2019 | 3 | B | 300 | B |
I've tried below expressions to add the “Commissions Category” into the summarized table:
commissions category = IF('Summarized'[sum of sales]>500,"A","B") , but it shows below errors:
cannot find name '[sum of sales]', anyone knows what is missing here or what are the alternative ways of doing this?
*We can't apply the expressions on the original raw table as it will be categorized by ROWS.
*We do not want to export/re-import the matrix as we would like the report to be refreshed monthly without additional steps to be done
Thank you.
