cancel
Showing results 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

Frequent Visitor

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

Thanks

1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
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

Super User

Hi @NDD

Can you post sample data as text and expected output?
Not enough information to go on;

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.

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

Community Support

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.