Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NDD
Frequent Visitor

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.

 

                        201920202021Forecast
Area110%13%12%11%
SubArea13%6%6%6%
SubArea25%4%3%3%
SubArea32%2%2%2%
SubArea40%1%1%0%
Area290%87%88%89%
SubArea547%43%44%51%
SubArea624%22%23%21%
SubArea715%11%13%12%
SubArea84%11%8%5%
Total100%100%100%100%

 

 

This is the data table.. there is only one.

 

Market    SubMarket   Area         SubArea     Year        Forecast/Historcial   Units
M1AArea1SubArea12019Historical50
M2BArea1SubArea22019Historical43
M3CArea1SubArea32019Historical67
M4DArea1SubArea42020Historical74
M1EArea2SubArea52020Historical31
M2FArea2SubArea12020Historical45
M3GArea2SubArea22021Historical32
M4HArea2SubArea32021Forecast36
M1IArea2SubArea42021Forecast43

 

Could someone please help me how to do this?

Thanks

 

1 ACCEPTED SOLUTION

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.

1.png

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.

2.png

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.

View solution in original post

4 REPLIES 4
v-rzhou-msft
Community Support
Community Support

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

VahidDM
Super User
Super User

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/

NDD
Frequent Visitor

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

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.

1.png

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.

2.png

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.