Creating a new row as the SUM of two rows for multiple measures

[ompowerbi]

Hi,

Possibly a complicated query here. I have several tables, please see below for reference. I have created visuals that summarise information based on the "Country" column. These visuals are matrices, which combine measures and already existing columns in my tables. I have to use the "Switch values to rows" button on these matrices so that they are formatted correctly from a visual perspective.

For instance, in a matrix, I use the country column from the "Summaries" table as my column headers and have added "Sales" column from the "Sales" table.. There are also measures included, that use data from multiple different tables. All are added to the matrix visual as "values". Using "Switch values to rows" this comes out looking like a table, where I need the column headers to be countries. 

My question is, what is the easiest way to create an additional column in the matrix which would give me the sales from England + Wales for example?

Essentially I would want to create an additional row in the tables, or a column in the matrix, that maps all data from england and wales and adds the numeric information together. 

I understand I could do this by getting my summary information into power query, which is reasonably simple for already existing table columns such as "sales" and doing a sumif in there. Then by transposing the table, I could add the England and Wales rows to create a new row. However, my measures are reasonably complicated in some instances and this could be difficult using m code. Also, I feel that some relationships may start to break in places.

So, is there a way to add the specific "England" and "Wales" data together to create this additional information in my matrix? Please let me know if anything needs to be clarified and thank you!

Summaries:

Country	Employees	Factories
England	100	4
Wales	50	2
Scotland	20	1

Sales:

Country	Factory ID	Manager	Sales	% Sales
England	E1	Aliyah Morales	17558	Cars
Wales	W1	Aaron Small	11486	Vans
Scotland	S1	Zaria Prince	12015	Toys
England	E2	Aron English	19033	Cars
England	E3	Kelly Sloan	15089	Cars
England	E4	Ocean Stark	10974	Cars
Wales	W2	Kamilah Pearson	14840	Vans
England	E1	Gunner Carlson	18665	Cars
Wales	W1	Kali Hodges	17421	Vans
Scotland	S1	Alonzo Wilcox	17307	Toys
England	E2	Ashlyn Wang	10192	Cars
England	E3	Cohen Decker	15698	Cars
England	E4	Aleena McKay	13543	Cars
Wales	W2	Joey Dickerson	19840	Vans
England	E1	Opal Guerrero	17862	Cars
Wales	W1	Bryce Wade	15712	Vans
Scotland	S1	Evie Palmer	15824	Toys
England	E2	Theo Lawrence	14129	Cars
England	E3	Lauren Graves	13489	Cars
England	E4	Cesar Ryan	14956	Cars
Wales	W2	Morgan Welch	19114	Vans

[ompowerbi]

The question proposed is fictional and if I were to comprehensively describe the intricacies of the issue, it would require a significant amount of time. As previously mentioned, the dataset and its underlying structures are rather complex, and to facilitate the process, I attempted to simplify the matter to minimize the time and effort required from any kind user who is willing to offer their help. My intention was to seek suggestions and ideas, and I appreciate your recommendation of the "group by" method. A switch function is used to display different currencies for each country. Please let me know if you have any quick suggestions through your experience as a super user, preferably talking about POWER BI.

[lbendlin]

I can help you find a solution for your scenario, and that solution doesn't necessarily have to involve Power BI. Power BI is a tool, it is not always the best tool for a job.

You can help by spending the effort to provide meaningful and covering sample data.

I have no quick suggestions.

[ompowerbi]

Can't do that unfortunately as E and W figures are in different currencies. Just wondering if there was a relatively easy way to do this. Not sure where data is put in twice?

[lbendlin]

E and W figures are in different currencies.

that is not obvious from your sample data. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

Create a Group field based on your Country column.

Placing the same data twice in a visual is a design red flag.