Help Needed - Sort Multiple Table based on value in table A

Question

Hi Everyone,&nbsp;I am working on Power BI Dashboard for Financial DataTable A: This year budgetProject Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Category A&nbsp; &nbsp; &nbsp;Category B&nbsp; &nbsp; &nbsp;Category C&nbsp; &nbsp;TotalMarketing X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.6Websites ZC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.4eCommerce&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.2Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3.2&nbsp;Table B: Last Year BudgetProject Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Category A&nbsp; &nbsp; &nbsp;Category B&nbsp; &nbsp; &nbsp;Category C&nbsp; &nbsp;TotalMarketing X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.0Websites ZC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.2eCommerce&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.3Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3.5&nbsp;We want Table A sorted based on Highest to Lowest for both column and row and Table B follow Table AExpected Result:Table A: This year budgetProject Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Category B&nbsp; &nbsp; &nbsp;Category A&nbsp; &nbsp; &nbsp;Category C&nbsp; &nbsp;TotalWebsites ZC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.4eCommerce&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.2Marketing X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.6Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.9&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3.2&nbsp;Table B: Last year budgetProject Name&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Category B&nbsp; &nbsp; &nbsp;Category A&nbsp; &nbsp; &nbsp;Category C&nbsp; &nbsp;TotalWebsites ZC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.2eCommerce&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.3Marketing X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.0Total&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;0.7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3.5&nbsp;Any thought how we can do this Dynamically?

amitchandak · Answer

Anonymous , If this a matrix column, it can only sort on sort column 
&nbsp;
Assume the data is like
Product Name, Category , Value
&nbsp;
Then you can create a new column
&nbsp;
Cat value= Sumx(filer(Table, [Category] = earlier([Category])) ,[Value])
Category 1 = [Category]
And mark cat value as sort column for Category 1, and use that in visual
&nbsp;
How to Create Sort Column and Solve Related Errors: https://www.youtube.com/watch?v=KK1zu4MBb-c

v-jingzhang · Answer

Hi&nbsp;Anonymous&nbsp;
&nbsp;
Currently the matrix visual only supports to sort by Row header and Value field. To sort the column, you need to add a calculated column in the data table to calculate this year's budget in advance, then sort the category column by it.&nbsp;

&nbsp;
Additionally, it is not supported to sort a matrix visual by values in another visual. You may also need to create a calculated column to sort by in advance. Does data in both visuals come from the same data table? Is the value field a measure or a column? Can you please share some dummy data to show the original data table then we can look into it if it is possible to create a calculated column for sorting-by?&nbsp;
&nbsp;
Best Regards, Community Support Team _ Jing

anonymous · Answer

Sure, my raw table consist of 2 sheet. First Sheet is project list, Second sheet is Category and Value like belowProject NameCategory20212022WebsitesX100200WebsitesY5010Project AX120250Project AY200400&nbsp;&nbsp;&nbsp;&nbsp;

Forum Discussion

Help Needed - Sort Multiple Table based on value in table A

3 Replies

Related content

regarding storing multiple tables in lake house

Use a table multiple time

Load multiple tables using query

Manage aggregations multiple fact tables DirectLake over OneLake

need help

Project Name	Category	2021	2022
Websites	X	100	200
Websites	Y	50	10
Project A	X	120	250
Project A	Y	200	400