Add columns from related rows in another table

Question

I have been using the matrix visual to display a set of columns from a related table. Table A contains columns ID, Name, Title. Table B contains columns ID, Category, Amount. There are 3-4 records in Table B for each ID in Table A. I put Name and Title as rows in my visual, Category as column and Amount as the value. This works okay, except I want to reorder the columns and I'd like to put Title as the last column in the visual. I don't think this is possible with a matrix, so now I am considering other possibilities.

One solution is adding columns in the data model to Table A for each Category and pulling in the Amount for each and then just using a standard table visual, but I'm not sure how to do this. I'm also not sure if it is the best approach.

Table A

Table B

Desired result

ashish_mathur · Answer

Hi,
This works with just one measure and no transformation in the Query Editor.&nbsp; Download the file from here.
Hope this helps.

jdbuchanan71 · Answer

Anonymous&nbsp;
One thing you could do is split the category into 3 measures so they could be pulled into a table visual without needing the category column.
First a measure just to sum the amount.
&nbsp;
Total Amount = SUM ( TableB[Amount] )

&nbsp;
Then the three measures for the categories&nbsp;
&nbsp;
Base = CALCULATE ( [Total Amount], KEEPFILTERS ( TableB[Category] = "Base" ) )Tax = CALCULATE ( [Total Amount], KEEPFILTERS ( TableB[Category] = "Tax") )Tip = CALCULATE ( [Total Amount], KEEPFILTERS ( TableB[Category] = "Tip" ) )
&nbsp;
Then in our table visual you pull in Name from the column, the 3 measures above (Base, Tax, Tip) and then the Title column
&nbsp;

mwegener · Answer

Hi,&nbsp;use Power Query.&nbsp;&nbsp;PBIX-File&nbsp;&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;Anonymous,&nbsp;To fix this, I would pivot your column category in Power Query Editor.&nbsp; In Power Query Editor you would highlight your category column, then go to Transform &gt; Any Column &gt; Pivot Column in the ribbon.&nbsp; You will get a box like the one below:&nbsp;&nbsp;&nbsp;Use your Amount column as the values and click OK. When you do this you will get a table that looks like the one below:&nbsp;Once you close and apply your work in the query editor, you will be able to easily put this information into the values of a table visual and move them in the order that you want.&nbsp;Hope this helps!

mwegener · Answer

Hi&nbsp;@kman42,as you can see, hwegman is of the same opinion.&nbsp;Please give Kudos for support.If I answered your question, please mark my post as solution, this will also help others.