Forum Discussion
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 in which is filterable by country.
So, for the following table:
| Country | Employee | City | Sales |
| Canada | A | Toronto | 79 |
| US | B | Boston | 31 |
| Australia | C | Brisbane | 15 |
| US | D | New York | 48 |
| Australia | A | Sydney | 17 |
| Canada | B | Vancouver | 12 |
| Australia | C | Melbourne | 94 |
| Canada | D | Montreal | 79 |
| US | A | Burlington | 22 |
| Australia | A | Canberra | 14 |
| Canada | B | Halifax | 67 |
| Australia | C | Melbourne | 49 |
| Canada | D | Calgary | 36 |
| US | A | Seattle | 71 |
| Canada | D | Regina | 98 |
| Australia | A | Sydney | 83 |
| Canada | D | Toronto | 93 |
| US | A | Boston | 84 |
So the report/matrix visual when filtered for US would give the following result:
| Employee | Cities | Sales |
| A | Boston, Burlington, Seattle | 177 |
| B | Boston | 31 |
| D | New York | 478 |
Similarly, filtering for Canada would give:
| Employee | Cities | Sales |
| A | Toronto | 79 |
| B | Halifax, Vancouver | 79 |
| D | Calgary, Montreal, Regina, Toronto | 306 |
For the matrix visual in Power BI, I used a measure to concatenate the cities, however, I still cannot use the concatenated cities as a row header/row option because Power BI won't let me use a measure as a row header. How can I do that? I know there's an option to use the value as a header but that doesn't allow me to only show the one value (Concatenated Cities) as a row header and the rest of them as values. How do I select a specific measure to show show as a row header and leave the other "values" (Sales, Quantity etc. as "values")? I want to see the breakdown by the employee and cities.
I"d appreciate any help!
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.
3 Replies
Hello newpbiuser01 , and thank you for sharing a question with the Community. This reply is informational. Please remember to adhere to the decorum of the Community Forum when asking a question.
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot). You can upload the PBIX file to a cloud storage service such as OneDrive, Google Drive, Dropbox, or to a Github repository, and then share a file’s URL.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you. Proud to be a Super User!
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:
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.
