topic Re: Using a Measure as a Row Header in DAX Commands and Tips

Using a Measure as a Row Header

newpbiuser01 — Mon, 08 Jul 2024 16:19:58 GMT

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!

Re: Using a Measure as a Row Header

foodd — Mon, 08 Jul 2024 16:23:40 GMT

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!

Re: Using a Measure as a Row Header

newpbiuser01 — Mon, 08 Jul 2024 17:58:27 GMT

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:

Re: Using a Measure as a Row Header

Anonymous — Tue, 23 Jul 2024 01:32:47 GMT

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.

Home (microsoft.com)

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.