Create different grouped rows in a Matrix Visual

[Simple_tuition1]

Hi,

lets say I have the following rows in a matrix visual with country and the sales person from the countries below:

USA	Sales Target	Sales Target Forcast
Jason	...	...
Ann	...	...
Mike	...	...
Nicole	...	...
Canada	...	...
Jake	...	...
Mia	...	...
Juliet	...	...
Kyle	...	...
Peru	...	...
Eduardo	...	...
Jacob	...	...
Carmen	...	...
Michael	...	...
Chile	...	...
Miguel	...	...
Julia	...	...
Lisa	...	...
France	...	...
Jeanne	...	...
Carl	...	...
Zita	...	...
Italy	...	...
Bruce	...	...
Claire	...	...
Florence	...	...
Japan	...	...
Pablo	...	...
Quentin	...	...
Sabrina	...	...

I want to create a row below the countries row looking like Sales by Country overall and the overalls for Sales in North America, Sales in South America, Sales in Europe and Sales in Asia with only the numbers from the countries in each region looking like this?

	Sales Target	Sales Target Forcast
USA
Canada
Peru
Chile
France
Italy
Japan
Sales by Countries overall
Sales in North America overall
Sales in South America overall
Sales in Europe overall
Sales in Asia overall

Is this possible? I could do this for overall since I added Countries and Salesperson below and if I collapse the matrix visual it shows the overall of all countries but how can I add the grouped calculation for each region below the matrix?

Thanks a lot and best regards

[MatthRichardsUK · Accepted solution]

Yes, that's correct. You can't directly add the region grouping as rows below the matrix visual. However, you can use measures to calculate the overall sales for each region and then add those measures to the matrix visual as value fields. This will achieve a similar effect to having the region grouping as rows below the matrix.

Here's an example of how you can create a measure to calculate the overall sales for the North America region:

Sales in North America overall =
SUMMARIZE(
'Table',
'Table'[Sales Target],
"Region", "North America"
)

To create measures for the other regions, you can use a similar formula and just replace "North America" with the name of the desired region.

Sales in South America overall =
SUMMARIZE(
'Table',
'Table'[Sales Target],
"Region", "South America"
)

Sales in Europe overall =
SUMMARIZE(
'Table',
'Table'[Sales Target],
"Region", "Europe"
)

Sales in Asia overall =
SUMMARIZE(
'Table',
'Table'[Sales Target],
"Region", "Asia"
)

Then, you can add these measures to the matrix visual as value fields and they will show the overall sales for each region.

I hope this helps!

[MatthRichardsUK]

Yes, it is possible to create the desired matrix visual in Power BI. 

1. Create a new column in your data table called "Region" and assign each country to a region (e.g. USA and Canada belong to North America).

2. In Power BI, add the "Region" column to your matrix visual as a row field.

3. Right-click on the "Region" row field and select "Add Total" > "After". This will add a row at the bottom of the matrix visual that shows the overall sales for each region.

4. To show the overall sales for all countries, add a row field for the "Country" column and then right-click on the "Country" row field and select "Add Total" > "After". This will add another row at the bottom of the matrix visual that shows the overall sales for all countries.

5. To show the overall sales for each region, you can use the SUMMARIZE function to create a measure that sums up the sales for each region. For example: Sales in North America overall = SUMMARIZE( 'Table', 'Table'[Sales Target], "Region", "North America" )

6. Add this measure to the matrix visual as a value field and it will show the overall sales for each region. You can create similar measures for the other regions as well.

I hope this helps! Let me know if you have any questions.

[Simple_tuition1]

Thanks for the fast reply. So this means I'm not able to directly add the region grouping as rows below the matrix visual?