topic Re: Matrix- Adding calculated column in DAX Commands and Tips

Matrix- Adding calculated column

NDD — Sun, 05 Dec 2021 20:26:18 GMT

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

Re: Matrix- Adding calculated column

VahidDM — Thu, 02 Dec 2021 12:23:15 GMT

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/

Re: Matrix- Adding calculated column

NDD — Sun, 05 Dec 2021 20:29:40 GMT

Hi VahidDM ,

Thank you for your response. I edited my post .The first table is the matrix visual and below that is the description of the additional columns that I need . The second table is my data .. I have only one table.

Thank you

Re: Matrix- Adding calculated column

Anonymous — Tue, 07 Dec 2021 09:46:29 GMT

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 Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Matrix- Adding calculated column

Anonymous — Tue, 14 Dec 2021 08:47:21 GMT

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