Forum Discussion
Matrix- Adding calculated column
Hi,
I have created a matrix visual to show the area and sub-area wise sales distribution as shown below. This matrix has several filters (country, market group, market) and I am showing the values as % of the column total. I would like to add two other columns to the existing matrix below.
Column 1 -To show the difference between Forecast vs 2021
Column 2-To show the difference between the forecast and the average of 2020 and 2021.
| 2019 | 2020 | 2021 | Forecast | |
| Area1 | 10% | 13% | 12% | 11% |
| SubArea1 | 3% | 6% | 6% | 6% |
| SubArea2 | 5% | 4% | 3% | 3% |
| SubArea3 | 2% | 2% | 2% | 2% |
| SubArea4 | 0% | 1% | 1% | 0% |
| Area2 | 90% | 87% | 88% | 89% |
| SubArea5 | 47% | 43% | 44% | 51% |
| SubArea6 | 24% | 22% | 23% | 21% |
| SubArea7 | 15% | 11% | 13% | 12% |
| SubArea8 | 4% | 11% | 8% | 5% |
| Total | 100% | 100% | 100% | 100% |
This is the data table.. there is only one.
| Market | SubMarket | Area | SubArea | Year | Forecast/Historcial | Units |
| M1 | A | Area1 | SubArea1 | 2019 | Historical | 50 |
| M2 | B | Area1 | SubArea2 | 2019 | Historical | 43 |
| M3 | C | Area1 | SubArea3 | 2019 | Historical | 67 |
| M4 | D | Area1 | SubArea4 | 2020 | Historical | 74 |
| M1 | E | Area2 | SubArea5 | 2020 | Historical | 31 |
| M2 | F | Area2 | SubArea1 | 2020 | Historical | 45 |
| M3 | G | Area2 | SubArea2 | 2021 | Historical | 32 |
| M4 | H | Area2 | SubArea3 | 2021 | Forecast | 36 |
| M1 | I | Area2 | SubArea4 | 2021 | Forecast | 43 |
Could someone please help me how to do this?
Thanks
Hi NDD ,
I think you built your matrix like me as below. You may add [Area] and [SubArea] in Rows, add [Year] in Columns and then add a Percentage measure in Values.
Now you want to add another two "columns" in your matrix format. If you create two measures, one to calculate by logic of column1 and another calculate by logic of column2, then add two measures in value field in matrix, you will get an expanded result by [Year]. It looks like as below.
Here I suggest you to remove [Year] in Columns from Matrix. Then create measures to calculate 2019,2020,2021... separately. Then add measures into Values, you will get the result you want.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Hi NDD
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/Hi NDD ,
I think you built your matrix like me as below. You may add [Area] and [SubArea] in Rows, add [Year] in Columns and then add a Percentage measure in Values.
Now you want to add another two "columns" in your matrix format. If you create two measures, one to calculate by logic of column1 and another calculate by logic of column2, then add two measures in value field in matrix, you will get an expanded result by [Year]. It looks like as below.
Here I suggest you to remove [Year] in Columns from Matrix. Then create measures to calculate 2019,2020,2021... separately. Then add measures into Values, you will get the result you want.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi NDD ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file without sensitive data.
Best Regards,Rico Zhou
