Forum Discussion
Create different grouped rows in a Matrix Visual
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
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!
