Forum Discussion
Solved
Using a Measure as a Row Header
Hello, I have a table that shows the Sales by the Employee, Country and City. I need to show a matrix visual as a report that breaks down the Sales by Employee and all the cities they had sales i...
Hi newpbiuser01 ,
Based on my testing, please try the following methods as workaround:
1.Create the sample table.
2.Create the new measure to calculate the sales, quantity and cities.
Sum of Sales = if(ISINSCOPE('Table'[Team])&&ISINSCOPE('Table'[Employee]),SUM('Table'[Sales]))Sum of Quantity = if(ISINSCOPE('Table'[Team])&&ISINSCOPE('Table'[Employee]),SUM('Table'[Quantity]))Cities = if(ISINSCOPE('Table'[Employee])&&ISINSCOPE('Table'[Team]),BLANK(),CONCATENATEX(VALUES('Table'[City]), 'Table'[City], ", "))3.Drag the measures into the matrix values.
4.Set the warp in the format settings.
5.Drag the left border to override the measure.
6.The result is shown below.
Besides, you can also raise a new idea and add the comments there to make the feature coming sooner.
Best Regards,
Wisdom Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello,
I am unable to upload a file unfortunately, but here is my data.
CountryEmployeeCitySalesQuantityTeam
| Canada | A | Toronto | 79 | 10 | Team A |
| US | B | Boston | 31 | 50 | Team B |
| Australia | C | Brisbane | 15 | 1 | Team A |
| US | D | New York | 48 | 3 | Team C |
| Australia | A | Sydney | 17 | 45 | Team A |
| Canada | B | Vancouver | 12 | 54 | Team B |
| Australia | C | Melbourne | 94 | 21 | Team C |
| Canada | D | Montreal | 79 | 89 | Team B |
| US | A | Burlington | 22 | 34 | Team A |
| Australia | A | Canberra | 14 | 1 | Team C |
| Canada | B | Halifax | 67 | 32 | Team B |
| Australia | C | Melbourne | 49 | 11 | Team B |
| Canada | D | Calgary | 36 | 20 | Team C |
| US | A | Seattle | 71 | 31 | Team A |
| Canada | D | Regina | 98 | 54 | Team B |
| Australia | A | Sydney | 83 | 64 | Team C |
| Canada | D | Toronto | 93 | 78 | Team A |
| US | A | Boston | 84 | 38 | Team C |
I only have one table, and one measure:
Cities:
Cities = CONCATENATEX(VALUES('Table'[City]), 'Table'[City], ", ")
I am trying to break the spend and quantity by Employee and show a concatenated list of their cities. However, as I mentioned, I am unable to use a measure as a row header. So what I get is:
What I need is: